VBA Code to disect and copy cell content

[mgirvin]

Dear Excel Team,

Can anyone give me some VBA code that would take a column filled with cells that have the strings “LastName FirstName MiddleInitial” (a space between each) in each cell and then separate them out into the three cells? The number of rows in the one column could vary.

[BigC]

Mike
Are you sure you need VBA, as this can be done very easily without VBA by using the Data / Text to Columns command?
1. Ensure you have anough blank columns to the right of your source data range as separated columns you require
2. Select the range to be split
3. Select Data / Text to Columns from the Main Menu
& follow the prompts.

If you need VBA to do this, try recording a macro as you manually work through the above process and see what code results.

[ravishankar]

Hi
Paste the following codes in the macro window ( alt F8)

x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
b = InStr(Cells(a, 1), " ")
c = InStrRev(Cells(a, 1), " ")
Cells(a, 2) = Left(Cells(a, 1), b)
Cells(a, 3) = Mid(Cells(a, 1), b, c - b)
Cells(a, 4) = Right(Cells(a, 1), Len(Cells(a, 1)) - c)
Next a

run the macro
Ravi

[mgirvin]

Wow! That Code is elegant. Thank you very much!

Thanks for the Text To Column also!

[krose]

What about if you have more than 3 items in the cell?

I have a cell with up tpo 6 items. There may be none, there may be six and they are all separated by a ", ".