Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Needs Help
 
Posts: n/a
Default 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   Report Post  
IanRoy
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Needs Help
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

"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   Report Post  
IanRoy
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
text from one column into multiple columns Jeff Brown Excel Discussion (Misc queries) 6 December 22nd 04 11:07 PM
Putting text in a column based on variable text from another colum Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 07:09 PM
how do I easily convert a single column of text (multiple rows si. philmah Excel Discussion (Misc queries) 2 December 16th 04 01:24 AM
Is there a way I can enter a letter to proceed text a column of ce Gordon Excel Discussion (Misc queries) 3 December 13th 04 08:13 PM
Problem with graph ranges No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM


All times are GMT +1. The time now is 08:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"