Is there a way to separate into own cells?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have xxx-xxx-xxxx(followed by a /) FirstName LastName xx-xxxx all in the
same cell (ie: 865-356-8434/Curtis Gogel 01-AUDT) but would like to have :
8653568434(no dashes)|Gogel, Curtis|01-AUDT.

Is it possible to "tweak" the excel cell info from ONE cell into three,
appearing as such listed above?
 
Assuming all of your data follows the same format as your example, these
formulas should work:

=SUBSTITUTE(LEFT(A1,12),"-","")

=MID(A1,FIND(" ",A1)+1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)&",
"&MID(A1,FIND("/",A1)+1,FIND(" ",A1)-FIND("/",A1)-1)

=RIGHT(A1,7)

HTH,
Elkar
 
Thank you Elkar. This is awesome.

Additionally, I just receive yet another excel file to do the vlook
(comparing one file to another) yet the problem now for IT is the fact that
that I need to separate the last name into first name into one cell (no coma
of course) last name into an additional cell. How best to tweat the formula
to reflect the separation of names into their own cell?
 
Easy enough, split the second formula into these two:

=MID(A1,FIND("/",A1)+1,FIND(" ",A1)-FIND("/",A1)-1)

=MID(A1,FIND(" ",A1)+1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

HTH,
Elkar
 
Thank you again! I found within the 2007 Excel Program an option that will
do some of the same things. Data, Text to Column option. Very awesome!
Great work truly!!!
 
Back
Top