Capture Space within String

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

My function SplitName below correctly parses a last/firstname string with or
without a comma between the name pair. The only problem I have is if the
last/first name is passed to the function with no "space" after the comma.
If you look at "TEST 2" below, you'll noticce that the first character "M"
in the first name variable gets cut off when the space after the separating
comma is not used.

Can someone help me modify my function so the function will correctly return
the last and first name no matter if it's used with or without the leading
space after the separating comma?


TEST 1: ****************

Call SplitName("MyLastName, MyFirstName")

Results:
Comma Found / Last Name: MyLastName
Comma Found / First Name: MyFirstName

TEST 2: ****************

Call SplitName("MyLastName,MyFirstName")

Results:
Comma Found / Last Name: MyLastName
Comma Found / First Name: yFirstName


CODE: *****************

Public Function SplitName(sName As String)

Dim intComma As Integer, sFirst As String, sLast As String

If IsNothing(sName) Then Exit Function
' Parse out first and last names

intComma = InStr(sName, ",")

If intComma = 0 Then
sLast = sName
Debug.Print "No Comma Found / Last Name: " & sLast
Else
sLast = Left(sName, intComma - 1)
Debug.Print "Comma Found / Last Name: " & sLast
sFirst = Mid(sName, intComma + 2)
Debug.Print "Comma Found / First Name: " & sFirst

End If

End Function
 
Can someone help me modify my function so the function will correctly return
the last and first name no matter if it's used with or without the leading
space after the separating comma?
Click to expand...

Try using Trim() to remove the space, if there is one:

Public Function SplitName(sName As String)

Dim intComma As Integer, sFirst As String, sLast As String

If IsNothing(sName) Then Exit Function
' Parse out first and last names

intComma = InStr(sName, ",")

If intComma = 0 Then
sLast = sName
Debug.Print "No Comma Found / Last Name: " & sLast
Else
sLast = Left(sName, intComma - 1)
Debug.Print "Comma Found / Last Name: " & sLast
sFirst = Trim(Mid(sName, intComma + 1))
Debug.Print "Comma Found / First Name: " & sFirst

End If

End Function

John W. Vinson [MVP]
 
thanks, i forgot about trim.


John W. Vinson said:
Try using Trim() to remove the space, if there is one:

Public Function SplitName(sName As String)

Dim intComma As Integer, sFirst As String, sLast As String

If IsNothing(sName) Then Exit Function
' Parse out first and last names

intComma = InStr(sName, ",")

If intComma = 0 Then
sLast = sName
Debug.Print "No Comma Found / Last Name: " & sLast
Else
sLast = Left(sName, intComma - 1)
Debug.Print "Comma Found / Last Name: " & sLast
sFirst = Trim(Mid(sName, intComma + 1))
Debug.Print "Comma Found / First Name: " & sFirst

End If

End Function

John W. Vinson [MVP]
Click to expand...
 
Back
Top