| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Elapsed time when separate cells contain time and separate date | New Users to Excel | |||
| Separate First Name MI and Last Name into three Cells | Excel Worksheet Functions | |||
| Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
| separate text into different cells | Excel Worksheet Functions | |||
| Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Linear Mode
