Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit/Replace to seperate text in columns
I currently have
John A Doe Sr. I want John A in Column A and Doe in Column B. I have attempted to do the split by: Duplicating the entire full name in two columns. In the first column I edit/replace by using the <space* command replacing with a blank and the first name is what is left. However, it does not allow me to <space*<space* to gather the middle initial as well. What else can I do in order to have the First Name and Middle initial in Column A and Last Name and possible suffix in Column B |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit/Replace to seperate text in columns
You can split it out into 2 columns using simple formulas, then, after
you're done you can convert the formulas to constants and delete the original data (after you inspect it and make sure everything looks OK!). A1 = John A Doe Sr. B1 = formula: =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) C1 = formula: =TRIM(SUBSTITUTE(A1,B1,"")) Select both B1 and C1 and copy down as needed. To convert to constants: Select the range of formulas Goto EditCopy Then, EditPaste SpecialValuesOK Inspect the data. If it's OK you can delete the original. Biff "Donna" wrote in message ... I currently have John A Doe Sr. I want John A in Column A and Doe in Column B. I have attempted to do the split by: Duplicating the entire full name in two columns. In the first column I edit/replace by using the <space* command replacing with a blank and the first name is what is left. However, it does not allow me to <space*<space* to gather the middle initial as well. What else can I do in order to have the First Name and Middle initial in Column A and Last Name and possible suffix in Column B |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit/Replace to seperate text in columns
Thank you, its been a few years since heavily using Excel and I lost some of
the tricks. "T. Valko" wrote: You can split it out into 2 columns using simple formulas, then, after you're done you can convert the formulas to constants and delete the original data (after you inspect it and make sure everything looks OK!). A1 = John A Doe Sr. B1 = formula: =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) C1 = formula: =TRIM(SUBSTITUTE(A1,B1,"")) Select both B1 and C1 and copy down as needed. To convert to constants: Select the range of formulas Goto EditCopy Then, EditPaste SpecialValuesOK Inspect the data. If it's OK you can delete the original. Biff "Donna" wrote in message ... I currently have John A Doe Sr. I want John A in Column A and Doe in Column B. I have attempted to do the split by: Duplicating the entire full name in two columns. In the first column I edit/replace by using the <space* command replacing with a blank and the first name is what is left. However, it does not allow me to <space*<space* to gather the middle initial as well. What else can I do in order to have the First Name and Middle initial in Column A and Last Name and possible suffix in Column B |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Edit/Replace to seperate text in columns
You're welcome. Thanks for the feedback!
Biff "Donna" wrote in message ... Thank you, its been a few years since heavily using Excel and I lost some of the tricks. "T. Valko" wrote: You can split it out into 2 columns using simple formulas, then, after you're done you can convert the formulas to constants and delete the original data (after you inspect it and make sure everything looks OK!). A1 = John A Doe Sr. B1 = formula: =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) C1 = formula: =TRIM(SUBSTITUTE(A1,B1,"")) Select both B1 and C1 and copy down as needed. To convert to constants: Select the range of formulas Goto EditCopy Then, EditPaste SpecialValuesOK Inspect the data. If it's OK you can delete the original. Biff "Donna" wrote in message ... I currently have John A Doe Sr. I want John A in Column A and Doe in Column B. I have attempted to do the split by: Duplicating the entire full name in two columns. In the first column I edit/replace by using the <space* command replacing with a blank and the first name is what is left. However, it does not allow me to <space*<space* to gather the middle initial as well. What else can I do in order to have the First Name and Middle initial in Column A and Last Name and possible suffix in Column B |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Columns | Excel Discussion (Misc queries) | |||
"Text to Columns" for many columns in Excel 2003 | Excel Discussion (Misc queries) | |||
Text Color Change prompted by different columns | Excel Worksheet Functions | |||
Messy Text to Columns | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions |