Jury Pool

[AdrianneAbel]

Hello. I'm in charge of creating a jury pool for the next trial and I'm hoping there is a macro or way that I can cut my data entry time in half. I have a sheet of 25,127 names that are all in one colomn, in this format (Smith, John J). I need them in three different colomns, each part of the name in it's own colomn. Is there a way I can automate this instead of cut and paste or retyping two of the fields? Lord please say yes...

thanks,
Adrianne

 

[onlyadrafter]

Hello and welcome to the board,

Look into DATA --> TEXT TO COLUMNS, with Delimited.

THis may cause other problems if name formats are different, i.e. no middle name, extra names etc..

 

[njimack]

Try the following formulae:

If A1=Smith, John J

To pickup surname, enter =LEFT(A1,FIND(",",A1,1)-1)
To pickup forname, enter=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))
To pickup middle name (initial), enter=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

 

[AdrianneAbel]

jury pool - thanks!

The text to colomns was great... thank you so much.