Splitting Text within a String and moving it's order

peterhay

New Member
Joined
May 1, 2007
Messages
26
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Peter

Perhaps:

Code:
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
 
Upvote 0

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

Something like:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,191,054
Messages
5,984,384
Members
439,883
Latest member
onions44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top