Splitting Text within a String and moving it's order

[peterhay]

Hi

I want to create a function to take a string of text e.g. "<surname>, <first name>" and be able to locate the <surname> i.e. characters before the comma and <first name> i.e. characters after the space after the comma and reverse them so that the output result will read <first name> <surname>.

Examples:
Input: "Smith, John" Expected Output: "John Smith"
Input: "Wilson, Alfred" Expected Output: "Alfred Wilson"

I can do this in an Excel formula. However, when I try and apply the same logic in a macro it does not like it.

Any help with this much appreciated
Peter

 

[Richard Schollar]

Hi Peter

Perhaps:

Dim l as long
l = Instr(1,Range("A1").Value,", ",vbBinaryCompare)
If l>0 Then 'ie ", " found in the string
   Range("B1").Value = Left(Range("A1").Value,l-1)
   Range("B2").Value = Mid(Range("A1").Value,l+2)
End If

 

[Darren Bartrup]

Hi Peter,

Something like:

Function SwapNames(sOriginal As String, Optional sSeperator As String)
    Dim lComma  As Long

    If sSeperator = "" Then
        sSeperator = ","
    End If
    
    lComma = InStr(sOriginal, sSeperator)
    SwapNames = Trim(Right(sOriginal, Len(sOriginal) - lComma)) & " " & _
        Trim(Left(sOriginal, lComma - 1))
End Function

 

[peterhay]

Hi Delmar

Your suggestion worked perfectly.

Many Thanks
Peter