Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! Last edited by Excelerated : September 13th 12 at 08:24 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
Just out of interest, what are you going to do with this data once altered? |
#5
|
|||
|
|||
[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. |
#6
|
|||
|
|||
Figured it out.
Thanks for the tip on trying to use Substitute |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine two cells without losing data cell formats | Excel Programming | |||
Concatenate first, middle and last names | Excel Discussion (Misc queries) | |||
combine cells with different formats | Excel Discussion (Misc queries) | |||
& to combine cells/Keep formats | Excel Discussion (Misc queries) | |||
Separating Full Names into First Middle and Last | Excel Discussion (Misc queries) |