Extract Names into Separate Columns

[mcalice]

I am trying to separate names into two or more different columns but am running into a problem.


My data is in Cell A1 and is formatted as Initials, a hyphen, then the first and last name. There is no space, only a comma, between the next name, so it looks like:

JED - John Doe,JMD - Jane Doe


The following formulas work great if I only have two names in A1:


I'm using this formula to locate everything LEFT of a comma and it works great.

'B1: =LEFT(A1,FIND(",",A1)-1)'


I'm using this formula to pull everything RIGHT of the comma, and it also works great.

'C1: =RIGHT(A1,LEN(A1)-FIND(",",A1)-1)'


My problem is, what do I do if I have THREE, or more, names separated by only a comma (no spaces)? Currently, the second formula "RIGHT" results in the right two names being placed in the same column.


I can't figure out how to separate this all out into individual columns.

Any help would be greatly appreciated!

 

[oldchippy]

Hi,


Have you tried Data>Text to Columns>Delimited, next,select comma, Finish

 

[mcalice]

Thanks oldchippy, I'll try your suggestion and see if I can use it as a manual workaround.

If anyone has any suggestions for how to write a formula for this I'd still prefer that as it wouldn't require me to do anything "manually" except for export/import from my database.


thanks!

Alice

 

[SirJB7]

Hi, mcalice!

It can be done if you have enough helper columns free.

Let's see. Assuming row 1 is for titles and that your data to be parsed is in column A from row 2 in advance, try this:


a) row 1

B1: Dummy

C1: Dummy

D1: =CONCATENAR("Comma ";(COLUMNA()-2)/2) -----> in english: =CONCATATE("Comma ",(COLUMN()-2)/2)

E1: =CONCATENAR("String ";(COLUMNA()-3)/2) -----> in english: =CONCATATE("String ",(COLUMN()-3)/2)


b) row 2

B2: 0

C2: no matters

D2: =SI(B2>LARGO($A2);"";HALLAR(",";$A2&",";B2+1)) -----> in english: =IF(B2>LEN($A2),"";SEARCH(",",$A2&",",B2+1))

E2: =SI(D2="";"";EXTRAE($A2&",";B2+1;D2-B2-1)) -----> in english: =IF(D2="","",MID($A2&",",B2+1,D2-B2-1))


c) copy columns D & E to the right as needed


d) copy down row 2 as needed


Regards!

 

[mcalice]

Wonderful! Thanks SirJB7, I think this is going to work perfectly!