Replace multiple spaces in names with different values

[BiggusDoggus]

Hi,

I have a substantial (and dynamic) list of names that I'm needing to transform into email addresses.

For names like Bob Smith, and even those with hyphens (Sarah-Jane Brown), it's no issue:

=LOWER(CONCAT(SUBSTITUTE(SUBSTITUTE(C16342," ","."),"-",""),"@abc.com"))

End result = bob.smith@abc.com, sarahjane.brown@abc.com

However, I also have names such as Stephen Ray Jones. I need this name to be stephenray.jones@abc.com.

How would I do this please? Essentially, remove the first space, and make the second space a ".".

 

[Colo]

Hi,
Replace the last space with a dot then remove unnecessary spaces or a hyphenation then.

Book1
	A	B
1	Bob Smith	bob.smith@abc.com
2	Sarah-Jane Brown	sarahjane.brown@abc.com
3	Stephen Ray Jones	stephenray.jones@abc.com
4	Stephen Ray Jones 3rd	stephenrayjones.3rd@abc.com
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ".", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))," ",""),"-","")) & "@abc.com"

 

[BiggusDoggus]

Hi,
Replace the last space with a dot then remove unnecessary spaces or a hyphenation then.

Book1
	A	B
1	Bob Smith	bob.smith@abc.com
2	Sarah-Jane Brown	sarahjane.brown@abc.com
3	Stephen Ray Jones	stephenray.jones@abc.com
4	Stephen Ray Jones 3rd	stephenrayjones.3rd@abc.com
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ".", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))," ",""),"-","")) & "@abc.com"

Fantastic - thank you!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example if you happen to have the latest 'TEXT' functions (not too many have them just yet though), you could also do it like this - Col B. (Also allows for one-word names if relevant)
I have also suggested an adjustment to @Colo's formula in case single-word names are possible - Col C

22 08 24.xlsm
	A	B	C
1	Bob Smith	bob.smith@abc.com	bob.smith@abc.com
2	Sarah-Jane Brown	sarahjane.brown@abc.com	sarahjane.brown@abc.com
3	Stephen Ray Jones	stephenray.jones@abc.com	stephenray.jones@abc.com
4	Stephen Ray Jones 3rd	stephenrayjones.3rd@abc.com	stephenrayjones.3rd@abc.com
5	Madonna	madonna@abc.com	madonna@abc.com
6	Sarah-Jane	sarahjane@abc.com	sarahjane@abc.com
Make email address

Cell Formulas
Range	Formula
B1:B6	B1	=LOWER(SUBSTITUTE(SUBSTITUTE(IFNA(TEXTBEFORE(A1," ",-1)&"."&TEXTAFTER(A1," ",-1),A1)," ",""),"-",""))&"@abc.com"
C1:C6	C1	=LOWER(SUBSTITUTE(SUBSTITUTE(IFERROR(SUBSTITUTE(A1, " ", ".", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))),A1)," ",""),"-","")) & "@abc.com"