Function to Combine Names and Middle Initital In Various Formats
 

Hi. Thanks for looking at my Thread. [I added spaces before @ signs 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 the format Ill be using most.

I figured out how to do this format using this function. I slightly 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 an 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 inititial with a decimal in between, if a middle initial is present. If there is 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.doe @xxx.c0m

I came across a formula online, closer to what I need, but it does not 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!

Function to Combine Names and Middle Initital In Various Formats
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Function to Combine Names and Middle Initital In Various Formats
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Just out of interest, what are you going to do with this data once altered?

[quote=Spencer101;1605479]Have a look at how to use SUBSTITUTE in your formula. That way you can 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.

Figured it out.

Thanks for the tip on trying to use Substitute