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

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

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

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

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

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