Function to Combine Names and Middle Initital In Various Formats

[Excelerated]

Hi. Thanks for looking at my Thread. [I added spaces before @ sign
because they were not showing.]
I have over 100k names I must combine into various formats.
For example
John Doe = JohnDoe @xxx.c0m
John Doe = John.Doe @xxx.c0m
John Doe = JDoe @xxx.c0m
John Doe = J.Doe @xxx.c0m
John Doe = Doe.J @xxx.c0m

The most common format is First Name(dot)Last Name and this is th
format Ill be using most.

I figured out how to do this format using this function. I slightl
modify it to give me the varying results needed:

LOWER(A1)&"."&LOWER(B1)&" @xxx.c0m"

This will give me something like: john.doe @xxx.c0m

The problem I am having is some names in the First Name column have a
middle initital. For example:
A B
1 John J Doe
So using the above formula I get:
john j.doe @xxx.c0m

I need a function that will combine the first name and middle inititia
with a decimal in between, if a middle initial is present. If there i
no middle initial, to continue with combining into the set format.

What I need is a formula that will take:
John Doe and/or John J Doe and create: john.doe or john.j.do
@xxx.c0m

I came across a formula online, closer to what I need, but it does no
do exactly what I need and Im getting confused modifying it:
=IF(ISNUMBER(LEN(A1)-FIND(" ",A1)),IF(LEN(A1)-FIND(
",A1)=1,A1&".",A1),A1)

Thank you very much

 

[Claus Busch]

Hi,

Am Thu, 13 Sep 2012 19:19:45 +0000 schrieb Excelerated:

What I need is a formula that will take:
John Doe and/or John J Doe and create: john.doe or john.j.doe
@xxx.c0m

try:
=LOWER(IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,1)&"."&B1,A1&"."&B1)&" @xxx.c0m")


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Thu, 13 Sep 2012 21:52:11 +0200 schrieb Claus Busch:

try:
=LOWER(IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,1)&"."&B1,A1&"."&B1)&" @xxx.c0m")

better and shorter:
=LOWER(SUBSTITUTE(A1," ",".")&"."&B1&" @xxx.c0m")


Regards
Claus Busch

 

[Excelerated]

Have a look at how to use SUBSTITUTE in your formula. That way you ca
swap the space for a full stop.

QUOTE]

Hi Spencer,

I will try this and get back to you with results.
Please check you IM box