By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
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
JLM
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
Share this Question
Share on Google+
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" <je**********@colostate.edu> 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
JLM
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" <sp**@nospam.spam> 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" <je**********@colostate.edu> 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" <je**********@colostate.edu> 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" <sp**@nospam.spam> 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" <je**********@colostate.edu> 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
JLM
Perfect..... thank you.

"PC Datasheet" <sp**@nospam.spam> 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" <je**********@colostate.edu> 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" <sp**@nospam.spam> 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" <je**********@colostate.edu> 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.