How to move one piece of text to another column

N

Needs Help

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
 
I

IanRoy

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.
 
M

Max

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)
 
N

Needs Help

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
 
M

Max

Needs Help said:
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
Click to expand...

You're welcome !
Glad to hear IanRoy's suggestion worked for you
Tinker with mine if you have the time
 
I

IanRoy

You are most welcome. :)

Needs Help said:
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
Click to expand...
 
Top