split "firstname lastname" into two cells

[bigdan]

I have a spreadsheet. Column A has the first name then last name, with just a space in the middle. I want to make these into 2 columns. How?

Thanks!

 

[RSXchin]

this puts first name in column A and last in Column B

Sub split_em_up()
     For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        SpacePlace = Application.WorksheetFunction.Search(" ", Cells(i, 1))
        FirstName = Left(Cells(i, 1), SpacePlace)
        LastName = Right(Cells(i, 1), SpacePlace)
        Cells(i, 1) = FirstName
        Cells(i, 2) = LastName
     Next i
End Sub

 

[hiker95]

bigdan,


Sample data before the macro:

Excel Workbook
	A	B
1	Name
2	big dan
3	hiker 95
4
Sheet1

After the macro:

Excel Workbook
	A	B
1	Name
2	big	dan
3	hiker	95
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitName()
' hiker95, 04/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=542776
Dim c As Range, SP
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  SP = Split(c, " ")
  c = SP(0)
  c.Offset(, 1) = SP(1)
Next c
End Sub

Then run the SplitName macro.

 

[Airfix9]

Why not just use Excel's native "Text to Columns" function? It's what it's there for!

 

[T. Valko]

I have a spreadsheet. Column A has the first name then last name, with just a space in the middle. I want to make these into 2 columns. How?

Thanks!

If the first names are always one word names...

Select the cells in question
Goto Data>Text to Columns
Delimited
Next
Select: Space
Finish

 

[RSXchin]

If the first names are always one word names...

Select the cells in question
Goto Data>Text to Columns
Delimited
Next
Select: Space
Finish

Well... YEAH, if you wanted to do it the EASY way.... But coding is like, more fun and stuff.

 

[bigdan]

wow you guys are so helpful - 5 responses in 12 mins!

im heading out of work right now, will look at these first thing in the morning. thanks so much guys!