Trying to Split First initial, middle initial, and Last name/Suffix with Excel Macro

Hi,

I am a Data analyst for a average-sized pharmaceutical company. I have been assigned the task of creating a macro the separates names. I could use Text to columns on the column of names that need to be separated, but I only need the space before the last name to be separated. So, in Column A would be the Full name(ex: J H williams III). In column B, the first an second initials would be located(ex: J H). The last name and suffix would be in the Column C. When I record a macro and use Text to Columns, I find that I can't specify for the macro to skip to the last name(basically the next word with 2 or more letters) and split there. Does anyone have a macro that can do this. Below is an example of how the data looks and how I need it to work.


K R Russell IV

The data needs to be split into columns on the same row

K R in Column B

&

Russell IV in column C


Any help would be appreciated.

Dameon

This is not in macro format (I rarely use macros), but you should get the idea anyway. Instead of using the Text function, use the Search function. This is similar to the Instr function in VB. The following formula could be placed in cell B1 to get the first two initials and no trailing space from cell A1 (the full name):

=LEFT(A1,SEARCH(" ",A1,3))

Then use the following formula in cell A3 to get the last name and suffix from A1:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,3))

Like I said, it's not a macro, but hopefully it will help. It assumes you will always have the "A B Lastname suffix" format. If you have to separate names instead of initials, use the Search function again, but change it to look for the first space to capture the first name, and look for the second space less the length of the first name to find the middle name. The same syntax could be used to separate the last name.

Thanks,

thecg

Hello Dameon,

Paste the code below to your excel macro. Remember to change the workbook and worksheet to the suitable name for your case. Hope it will help.
'------------

Sub Macro1()

Dim MySheet As Worksheet
Dim RowNumber As Integer

Const ColumnA = 1
Const ColumnB = 2
Const ColumnC = 3
Set MySheet = Workbooks("Book1.xls").Worksheets("Sheet1")
With MySheet
RowNumber = 1
While Not IsEmpty(.Cells(RowNumber, ColumnA))
.Cells(RowNumber, ColumnB) = Left(.Cells(RowNumber, ColumnA), 3)
.Cells(RowNumber, ColumnC) = Mid(.Cells(RowNumber, ColumnA), 4)
RowNumber = RowNumber + 1
Wend
End With

'Application.Quit

End Sub
'------------