cell text separation

Replies to This Discussion

Permalink Reply by Daniel Chow on January 22, 2010 at 8:48pm

You could try the "Text to Columns" function, using a space as a separator:

Data --> Text to Columns --> Delimited Width --> Use Space as Delimiter

▶ Reply to This

Permalink Reply by Dipu James on January 22, 2010 at 10:36pm

After daniel

Alt + D + E

Delimit using space

▶ Reply to This

Permalink Reply by lilolee on January 23, 2010 at 4:23am

However if one of the names has a middlename then it goes wrong, as it will mix up middle and surname in the same column.

Try this thread for a formula solution http://www.mrexcel.com/forum/showthread.php?t=9179

▶ Reply to This

Permalink Reply by Danielle Stein Fairhurst on January 24, 2010 at 3:40am

Hi Tim,
You could just do a text to columns split, but if you specifically want a formula, there is one you can use.
Assuming "Brian Summers" appears in cell A1, you can get the value "Brian" by the formula =LEFT(A1,FIND(" ",A1)-1) and the value "Summers" by the formula =RIGHT(A1,FIND(" ",A1)+1).
I hope that helps. Of course if you have a middle name, it will just pick up the first and last word.
For a list of free Microsoft Excel tutorials, see: http://www.plumsolutions.com.au/tutorials
Danielle Stein Fairhurst
www.plumsolutions.com.au

▶ Reply to This

Permalink Reply by Tim Simpson on January 25, 2010 at 10:04am

Thank you Danielle, this was exactly what i was looking for

▶ Reply to This

Permalink Reply by Irshad Aranhikkal on March 12, 2010 at 1:53am

Dear Simpson You have shown partiality towards me. I gave you correct answer but you didnt appreciated me as you appreciated Miss Daniella and also you have appreciated Daniella without verifying how the formula works. I will bet you that the formula given by Daniella for returning the second name will work only in the first instance it wont work in the second and subsequent instance which i gave you . Only my formula will work for second name.

▶ Reply to This

Permalink Reply by Dipu James on January 27, 2010 at 2:12am

this is cool thank you Danielle

▶ Reply to This

Permalink Reply by Irshad Aranhikkal on March 12, 2010 at 2:04am

Hi Daniella your formula for returning second name will not work properly in all instances please see the attached sheet to see how your formula works and what is the right formula

Attachments:

• exceljockeys.xls, 14 KB

▶ Reply to This

Permalink Reply by Rolando Matutis on January 24, 2010 at 4:51am

It is called as "Convert Text to Column Wizard".

You just have to Fixed the width of the text by dragging the line with arrow representing
the column break. Just go to help and type the above. Try to open the uploaded file how
it looks like. Or refer above for guidance. Just click nexxt to proceed further.

Attachments:

• print screen.bmp, 813 KB

▶ Reply to This

Permalink Reply by Richard Saint-Amant on January 25, 2010 at 7:46am

1st, verify that there are no more than two words in each cell.
2nd, to the right of the column containing the names (for illustration, call it column A), insert two blank columns (B and C).
3rd, click on the top of column A to select the entire column.
4th, in Excel 2000 and 2003, click on Data, Text to Columns, and, as the delimiter, uncheck 'comma' and check 'space'.
Click Okay

▶ Reply to This

Permalink Reply by Irshad Aranhikkal on February 7, 2010 at 12:26am

Dear Tim this the answer of ur query please acknowledge this to me through mail irshadedv@gmail.com

Attachments:

• excel jockeys.xls, 14 KB

▶ Reply to This

Permalink Reply by Tim Simpson on February 8, 2010 at 9:57am

yes, someone else reccommended this, thank you for your post

▶ Reply to This

• ‹ Previous
• 1
• 2
• Next ›
• Page