SOLVED

Need to concatenate cells but limit number of characters from each original cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1240256%22%20slang%3D%22en-US%22%3EHelp%20to%20concatenate%20cells%20but%20limit%20number%20of%20characters%20in%20new%20cell%20from%20each%20original%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1240256%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20save%20time%20creating%20new%20email%20addresses%20by%20combining%20cells%20containing%20first%20name%20last%20name%20and%20a%20number.%26nbsp%3B%20Trying%20to%20do%20this%20using%20the%20concatenate%20function%20but%20can't%20work%20out%20how%20to%20limit%20the%20number%20of%20characters%20taken%20from%20each%20cell%20to%203%20characters.%20E.g%20if%203%20different%20cells%20contain%20data%3A%20Josephine%2C%20Bloggs%2C%2044449587.%26nbsp%3B%20I%20need%20to%20take%20the%20first%203%20letters%20of%20names%20(Jos%2CBlo)%20and%20the%20last%203%20digits%20of%20the%20number%20(587)%20and%20combine%20to%20form%20an%20email%20address%3A%26nbsp%3B%3CA%20href%3D%22mailto%3Ajosblo587%40example.org.uk%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ejosblo587%40example.org.uk%3C%2FA%3E%26nbsp%3Band%20repeat%20for%20a%20large%20data%20set.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20speed%20up%20my%20work%20ridiculously%20if%20i%20could%20sort%20this%20out%2C%20thank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1240256%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1240434%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20concatenate%20cells%20but%20limit%20number%20of%20characters%20from%20each%20original%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1240434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F587780%22%20target%3D%22_blank%22%3E%40Flubberduck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20A2%2C%20B2%20and%20C2%20have%20these%20three%20pieces%20of%20information%20you%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOWER(LEFT(A2%2C3)%26amp%3BLEFT(B2%2C3)%26amp%3BRIGHT(C2%2C3)%26amp%3B%22example.org.uk%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Trying to save time creating new email addresses by combining cells containing first name last name and a number.  Trying to do this using the concatenate function but can't work out how to limit the number of characters taken from each cell to 3 characters. E.g if 3 different cells contain data: Josephine, Bloggs, 44449587.  I need to take the first 3 letters of names (Jos,Blo) and the last 3 digits of the number (587) and combine to form an email address: josblo587@example.org.uk and repeat for a large data set.

 

It would speed up my work ridiculously if i could sort this out, thank you 

1 Reply
Highlighted
Solution

@Flubberduck 

If A2, B2 and C2 have these three pieces of information you may try something like this...

 

=LOWER(LEFT(A2,3)&LEFT(B2,3)&RIGHT(C2,3)&"example.org.uk")