445,841 Members | 1,757 Online
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

# Split a field based on a character

 P: n/a Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Nov 12 '05 #1
6 Replies

 P: n/a Use the InStr function to find the comma: MyLastName = Left([NameField],Instr([NameField],",")-1)) -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com "JLM" wrote in message news:40******@news.ColoState.EDU... Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Nov 12 '05 #2

 P: n/a Use x = Instr(myfield,",") to find the comma position. Then use Left(myfield, x-1) to get the firstname and Mid(myfield, x+1 ,len(myfield)) to get the second bit. Peter Russell JLM previously wrote: Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Nov 12 '05 #3

 P: n/a Thanks, this helps a lot. However, this works only for the left side of the comma. I've messed with it and can't find the answer to get the right side of the comma (less a single space). jlm "PC Datasheet" wrote in message news:ML*******************@newsread1.news.atl.eart hlink.net... Use the InStr function to find the comma: MyLastName = Left([NameField],Instr([NameField],",")-1)) -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com "JLM" wrote in message news:40******@news.ColoState.EDU... Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Nov 12 '05 #4

 P: n/a On Mon, 23 Feb 2004 08:01:43 -0700, JLM wrote: Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Without a space after the comma? LastName= Left([FullName],InStr([FullName],",")-1) FirstName:Mid([FullName],InStr([FullName],",")+1) -- Fred Please only reply to this newsgroup. I do not reply to personal email. Nov 12 '05 #5

 P: n/a Peter's Mid function will do it for you. I just noticed that what I sent you should have said MyFirstname instead of MyLastName. Here's Peter's Mid function for the last name: MyLastName = Mid([NameField], Instr([NameField],",")+1 ,Len([NameField])) Steve PC Datasheet "JLM" wrote in message news:40******@news.ColoState.EDU... Thanks, this helps a lot. However, this works only for the left side of the comma. I've messed with it and can't find the answer to get the right side of the comma (less a single space). jlm "PC Datasheet" wrote in message news:ML*******************@newsread1.news.atl.eart hlink.net... Use the InStr function to find the comma: MyLastName = Left([NameField],Instr([NameField],",")-1)) -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com "JLM" wrote in message news:40******@news.ColoState.EDU... Haven't seen this question yet. I have a field that has "LastName,FirstName". I want to split that field into two, based on the comma. Size of LastName is obviously different in each record, so Left() won't work. Any ideas? thanks in advance, jlm Nov 12 '05 #6

 P: n/a Perfect..... thank you. "PC Datasheet" wrote in message news:P6******************@newsread1.news.atl.earth link.net... Peter's Mid function will do it for you. I just noticed that what I sent you should have said MyFirstname instead of MyLastName. Here's Peter's Mid function for the last name: MyLastName = Mid([NameField], Instr([NameField],",")+1 ,Len([NameField])) Steve PC Datasheet "JLM" wrote in message news:40******@news.ColoState.EDU... Thanks, this helps a lot. However, this works only for the left side of the comma. I've messed with it and can't find the answer to get the right side of the comma (less a single space). jlm "PC Datasheet" wrote in message news:ML*******************@newsread1.news.atl.eart hlink.net... Use the InStr function to find the comma: MyLastName = Left([NameField],Instr([NameField],",")-1)) -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com "JLM" wrote in message news:40******@news.ColoState.EDU... > Haven't seen this question yet. > > I have a field that has "LastName,FirstName". I want to split that field > into two, based on the comma. Size of LastName is obviously different in > each record, so Left() won't work. Any ideas? > > thanks in advance, > jlm > > Nov 12 '05 #7

### This discussion thread is closed

Replies have been disabled for this discussion.