Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to move one piece of text to another column
Ok Ill explain what im doing to help understand how to help. I have a column
that lists last names then a space then a middle initial(Example column A = David H). I need to turn that into 2 columns, one with last name, then the other with middle initial. The catch is not all have a middle initial ( some just say David). There are 4400 entries which would make it a long time to do by hand. I would greatly appreciate any expierenced excels users help. If there is not a way to do it in excel, can it be done in access? Thanks soooo much |
#2
|
|||
|
|||
Select your column. Then from the menu bar: Data Text to Columns Delimited
Next Space Next choose a destination (it will overwrite the next column, so insert a blank one first) and Finish. "Needs Help" wrote: Ok Ill explain what im doing to help understand how to help. I have a column that lists last names then a space then a middle initial(Example column A = David H). I need to turn that into 2 columns, one with last name, then the other with middle initial. The catch is not all have a middle initial ( some just say David). There are 4400 entries which would make it a long time to do by hand. I would greatly appreciate any expierenced excels users help. If there is not a way to do it in excel, can it be done in access? Thanks soooo much |
#3
|
|||
|
|||
Just another way to try ..
Assuming the list is in col A, data from row2 down Put in B2: =IF(ISERROR(SEARCH(" ",TRIM(A2))),TRIM(A2),LEFT(TRIM(A2),SEARCH(" ",TRIM(A2))-1)) Put in C2: =IF(ISERROR(SEARCH(" ",TRIM(A2))),"",MID(TRIM(A2),SEARCH(" ",TRIM(A2))+1,99)) Select B2:C2, fill down Kill the formulas in cols B and C with an "in-place": Copy Paste special Values OK (then delete col A, if desired) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Needs Help" wrote: Ok Ill explain what im doing to help understand how to help. I have a column that lists last names then a space then a middle initial(Example column A = David H). I need to turn that into 2 columns, one with last name, then the other with middle initial. The catch is not all have a middle initial ( some just say David). There are 4400 entries which would make it a long time to do by hand. I would greatly appreciate any expierenced excels users help. If there is not a way to do it in excel, can it be done in access? Thanks soooo much |
#4
|
|||
|
|||
Thanks so much for the help. It worked beautifully. I havent tried maxs way
because i was confuesd but it looked interesting. Thanks so much both of you "IanRoy" wrote: Select your column. Then from the menu bar: Data Text to Columns Delimited Next Space Next choose a destination (it will overwrite the next column, so insert a blank one first) and Finish. "Needs Help" wrote: Ok Ill explain what im doing to help understand how to help. I have a column that lists last names then a space then a middle initial(Example column A = David H). I need to turn that into 2 columns, one with last name, then the other with middle initial. The catch is not all have a middle initial ( some just say David). There are 4400 entries which would make it a long time to do by hand. I would greatly appreciate any expierenced excels users help. If there is not a way to do it in excel, can it be done in access? Thanks soooo much |
#5
|
|||
|
|||
"Needs Help" wrote:
Thanks so much for the help. It worked beautifully. I havent tried maxs way because i was confused but it looked interesting. Thanks so much both of you You're welcome ! Glad to hear IanRoy's suggestion worked for you Tinker with mine if you have the time -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
You are most welcome. :)
"Needs Help" wrote: Thanks so much for the help. It worked beautifully. I havent tried maxs way because i was confuesd but it looked interesting. Thanks so much both of you "IanRoy" wrote: Select your column. Then from the menu bar: Data Text to Columns Delimited Next Space Next choose a destination (it will overwrite the next column, so insert a blank one first) and Finish. "Needs Help" wrote: Ok Ill explain what im doing to help understand how to help. I have a column that lists last names then a space then a middle initial(Example column A = David H). I need to turn that into 2 columns, one with last name, then the other with middle initial. The catch is not all have a middle initial ( some just say David). There are 4400 entries which would make it a long time to do by hand. I would greatly appreciate any expierenced excels users help. If there is not a way to do it in excel, can it be done in access? Thanks soooo much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text from one column into multiple columns | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) | |||
Is there a way I can enter a letter to proceed text a column of ce | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel |