Multiple Commas

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.
 
D

DrGUI

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)
 
J

Jerry Whittle

After splitting the name fied up, do a Replace on the First Name field to get
rid of the comma.
 
M

Marshall Barton

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?


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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top