Multiple Commas

  • Thread starter Thread starter mdkryptoking
  • Start date Start date
M

mdkryptoking

Some people decided to play a cruel joke and pulled two commas into a name
field and i need to split the name into a first and last name column.
Normally i could do this but the second comma is throwing me off. It looks
like this:

last name,first name,

Ive tried doing a replace on the last comma but its not working. Does anyone
know any tricks on how to either get rid of that second comma or split the
field into two seperate columns as it is? Thanks for any help.
 
dim aName() as string
dim strName as string
dim strLastName as string
dim strFirstName as string

' just to be sure, remove any trailing spaces
' from input field [Name]
strName = trim([Name])
' check last character in string
if right(strName,1)="," then
strName = left(strName,len(strName)-1)
endif

' split string and store in array (delimiter is comma)
aName = split(strName,",")

' get first/last name from array (zero based)
strLastName = aName(0)
strFirstName = aName(1)
 
After splitting the name fied up, do a Replace on the First Name field to get
rid of the comma.
 
mdkryptoking said:
Some people decided to play a cruel joke and pulled two commas into a name
field and i need to split the name into a first and last name column.
Normally i could do this but the second comma is throwing me off. It looks
like this:

last name,first name,

Ive tried doing a replace on the last comma but its not working. Does anyone
know any tricks on how to either get rid of that second comma or split the
field into two seperate columns as it is?
Click to expand...


LastName: Left(namefield, InStr(namefield, ",")-1)

FirstName: Left(Mid(namefield, InStr(namefield, ",")+1),
InStr(Mid(namefield &",", InStr(namefield, ",")+1), ",")-1)
 
Back
Top