• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Switch around characters within a string (VBA)

Hello  I have a string which always outputs

Surname, firstname


I would like to change the string to Firstname Surname using VBA. Changing around the firstname and surname and removing the ( , ) as well.

Can someone help with this ?

Thanks,

GISVPN
0
gisvpn
Asked:
gisvpn
  • 4
  • 3
1 Solution
 
sam_norianCommented:
Try This:

        Dim OLDString As String
        Dim NewString As String
        Dim commaPos As String
        Dim SirName As String
        Dim FirstName As String

        OLDString = "Criddle, Natalie" 'Existing String

        commaPos = InStr(4, OLDString, ",", 1) 'Get Comma Position

        SirName = Left(OLDString, Val(commaPos - 1)) 'Get Sirname

        FirstName = Right(OLDString, Val(OLDString.Length - commaPos)) ' Get First Name

        NewString = FirstName & " " & SirName

        MsgBox(NewString)
0
 
gisvpnAuthor Commented:
Hi thanks for the help - I am getting an erorr on this line - im not sure why

  FirstName = Right(OLDString, Val(OLDString.Length - commaPos)) ' Get First Name

Object required ?
0
 
sam_norianCommented:
what version of vb are you using?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gisvpnAuthor Commented:
this is VBA in Word
0
 
sam_norianCommented:
Sorry should use len() in vba, I didn't read your question properly.

Should Be:

        Dim OLDString As String
        Dim NewString As String
        Dim commaPos As String
        Dim SirName As String
        Dim FirstName As String

        OLDString = "LastName, FirstName" 'Existing String
        commaPos = InStr(4, OLDString, ",", 1) 'Get Comma Position
        SirName = Left(OLDString, Val(commaPos - 1)) 'Get Sirname

        FirstName = Right(OLDString, Val(Len(OLDString) - commaPos)) ' Get First Name

        NewString = FirstName & " " & SirName

        MsgBox (NewString)
0
 
gisvpnAuthor Commented:
perfect thanks :)
0
 
sam_norianCommented:
No probs, thanks for the grade.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now