VBA Split Function

[heimdaloz]

Hi,

I have a text value in a variable that contains a name. The name is in the format First, Middle, Last, however, Middle is optional.

I have used a split function to break the names in their individual components are output into columns.

If the name contains First, Middle, Last, I output the values into separate columns using Split.

However, if the name only contains First and Last, I want the Last to be recorded in the last column instead of the middle column.

For example[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]First[/TD]
[TD]Middle[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]John Mark Smith[/TD]
[TD]John[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Hugo Harris[/TD]
[TD]Hugo[/TD]
[TD]Harris[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]First[/TD]
[TD]Middle[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]John Mark Smith[/TD]
[TD]John[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Hugo Harris[/TD]
[TD]Hugo[/TD]
[TD][/TD]
[TD]Harris[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions appreciated.

 

[Joe4]

Welcome to the Board!

Try this (assuming data start in cell A2, and you want the names in columns B, C, and D):

Sub MySplit()

    Dim lrow As Long
    Dim r As Long
    Dim myArray() As String
    Dim mx As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting on row 2
    For r = 2 To lrow
'       Split entry in column A on the space
        myArray = Split(Cells(r, "A"), " ")
'       See how many items there are
        mx = UBound(myArray)
'       Split for two words
        If mx = 1 Then
            Cells(r, "B") = myArray(0)
            Cells(r, "D") = myArray(1)
        Else
'       Split for three words
            Cells(r, "B") = myArray(0)
            Cells(r, "C") = myArray(1)
            Cells(r, "D") = myArray(2)
        End If
'       Reinitialize array for next run
        Erase myArray
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[heimdaloz]

Thanks, Joe4,

I need to split a string from an array, your code wasn't exactly what I needed, but I was able to use your code to write what I needed.

Thank you very much

 

[Rick Rothstein]

Here is another macro that you can consider (and modify for your array situation)...

[table="width: 500"]
[tr]
	[td]Sub SplitNames()
  Dim R As Long, Nam As String
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Nam = Cells(R, "A").Value
    If Not Nam Like "* * *" Then Nam = Replace(Nam, " ", Space(2))
    Cells(R, "B").Resize(, 3) = Split(Nam)
  Next
End Sub[/td]
[/tr]
[/table]

 

[Joe4]

I need to split a string from an array, your code wasn't exactly what I needed, but I was able to use your code to write what I needed.

I am curious. Can you provide an example where it wouldn't work? It worked on the examples you provided.
Can you also post the changes you made to the code?