Is there a way to separate into own cells?
 

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?

Is there a way to separate into own cells?
 

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

"Penny" wrote:

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?

Is there a way to separate into own cells?
 

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?

"Elkar" wrote:

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

"Penny" wrote:

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?

Is there a way to separate into own cells?
 

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


"Penny" wrote:

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?

"Elkar" wrote:

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

"Penny" wrote:

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?

Is there a way to separate into own cells?
 

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!!!

"Elkar" wrote:

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


"Penny" wrote:

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?

"Elkar" wrote:

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

"Penny" wrote:

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?