Split the contents of a field ie - full name into first and last n

[CJH]

I have data that we import into a table. The raw data has full name. I want
to split the full name into first and last name. There are spaces between
the first and last name in the fullname field. Is there a way to do this?

Thanks in advance,

Clay H.

 

[John Spencer]

Yes and no. If your full name ALWAYS consists of a one word first name,
followed by a space then you can use expressions like the following

FirstName: Trim(Left([FullName], Instr(1,[FullName], " ")))
LastName: Trim(Mid([FullName], Instr(1,[FullName], " ")))

Note that this will fail with names like
Bobby Joe Fallon
Mary Ellen Stewart
etc.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Pendragon]

FirstName: left$([FullName], InStr([FullName]," "))
LastName: Trim(mid$([FullName], InStr([FullName], " "),99))

Access (in my experience - and MVPs will please correct me if I am wrong)
will drop the space from the _end_ of a string, so the Trim function is not
necessary on the FirstName field. It is necessary on the LastName field
otherwise your last names would begin with a space. You could also put a +1
after the InStr() function in LastName, but that is successful only if there
is always and ONLY 1 space in between first and last name.

I use 99 to cover all characters after my InStr() position. There is a
method to calculate exactly how many characters are left in the string after
the space, but I find using 99 to be simplest.

HTH.

 

[John Spencer]

You can leave off the last argument in MID if you want all the remaining
characters. I'm not sure that works with MID$ (never tested it).

Just tested it and that works.

Access will trim trailing spaces and other characters when you enter from the
keyboard. It doesn't always trim trailing spaces when you use a query or VBA.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

FirstName: left$([FullName], InStr([FullName]," "))
LastName: Trim(mid$([FullName], InStr([FullName], " "),99))

Access (in my experience - and MVPs will please correct me if I am wrong)
will drop the space from the _end_ of a string, so the Trim function is not
necessary on the FirstName field. It is necessary on the LastName field
otherwise your last names would begin with a space. You could also put a +1
after the InStr() function in LastName, but that is successful only if there
is always and ONLY 1 space in between first and last name.

I use 99 to cover all characters after my InStr() position. There is a
method to calculate exactly how many characters are left in the string after
the space, but I find using 99 to be simplest.

HTH.

I have data that we import into a table. The raw data has full name. I want
to split the full name into first and last name. There are spaces between
the first and last name in the fullname field. Is there a way to do this?

Thanks in advance,

Clay H.