split a firstname and surname in a cell into 2 separate cells

[severet93]

Hi, I have to split a cell with a first name and a surname into separate cells ,eg
the name in cell A1 I would like to split into 2 cells say A2 and A3, is there a macro I can do that with ?
Thankyou in advance

[jmac1947]

Hi,
what is the format of the name,
how are the two parts differentiated,
are ALL examples of the joined names IDENTICAL in structure,
how many rows would you expect
where does the data come from

depending on the answers above, you can easily do it with a macro or simply using a formula and copying the formula down you spreadsheet

Jmac

[severet93]

Hi Thanks for quick reply!
Name format example Biseell Metehne
differentiated by a space
Identical Yes
rows 100
not exactly sure what you mean.

[FDibbins]

THis woud be a simple process using Text2Columns, using "space" as the delimiter

[jmac1947]

I can see why Ford is a serious moderator.

My last question was trying to see if this is a "one off" problem you have or is likely to be a regular task, that often influences suggestions on a solution.

Given your answers, the suggestion from Ford in post #4 sounds like a good solution

Text 2 columns

To do this, use Text to Columns. Select the column where you have the names, go to Data, Text to Columns, select Delimited in Step 1, in Step 2 make sure that the space checkbox is is the only one activated. Finally, in step 3 select the Destination cell (It defaults to the current cell so select the columns you need). Hit Finish and that's it.

[FDibbins]

jmac, you make a very good point, that if this is a recurring process, even though T2C is quick, it can become laborious over time. As a once-off (or just now and then), it will work just fine, otherwise a formula route might be better. Something like...
=LEFT(A1,FIND(" ",A1,1)-1)
and
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

[jmac1947]

Ford, agreed but I am thinking that for 100 rows either way is probably going to be OK, if the data is externally generated then T2C is probably slightly infront, if the OP is cutting data into a static spreadsheet then (provided there is a "safe place" to store the formula then copy, drag,paste is very quick

Jmac

[severet93]

oh gosh should have checked text to columns first thankyou for that.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.