Split combined name (Smith, John) into (Smith) (John)?

[Guest]

Is there a faster way, other than a lot of retyping, to split a column which
has our customer names in the (Smith, John) format into 2 columns (Smith)
(John).

The data originated from an Excel spreadsheet and we need it in a Last Name,
First Name format.

Thank you.

 

[Brendan Reynolds]

An update query ...

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

See Left Function, InStr Function, and Mid Function in the help files for
more information.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Hi,
You can use "InStr" to split a fied. The only problem is that if there is
record not in the for "LastName, FirstName" then you will have to do extra
coding.

This query will split the Customer Name in two fields.

Select trim(mid(CustomerName, 1, InStr(CustomerName, ",") - 1)) as LastName,
trim(mid(CustomerName, InStr(CustomerName, ",") + 1)) as FirstName
From CustomerTable

Hope this helps.

 

[Marshall Barton]

Is there a faster way, other than a lot of retyping, to split a column which
has our customer names in the (Smith, John) format into 2 columns (Smith)
(John).

The data originated from an Excel spreadsheet and we need it in a Last Name,
First Name format.

As long as you can rely on the comma being there, you can
parse the string in an Update query. Try something like
this on a copy of your table:

UPDATE table
SET (FName = Trim(Mid(fullname, InStr(fullname, ",")+1)),
LName = Trim(Left(fullname, InStr(fullname, ",")-1)))
WHERE InStr(fullname, ",") > 0

 

[Jamie Collins]

InStr(1,[FullName],",")

Ah, one can spot another that uses Jet queries outside of the MS Access UI <g>.

Jamie.

--

 

[Brendan Reynolds]

You mean the use of the optional Start parameter?

I'm not quite sure why I'm in the habit of using that, except that an
optional parameter that comes *before* the required parameters just doesn't
seem natural somehow!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

InStr(1,[FullName],",")

Ah, one can spot another that uses Jet queries outside of the MS Access UI
<g>.

Jamie.

--

 

[Jamie Collins]

You mean the use of the optional Start parameter?
I'm not quite sure why I'm in the habit of using that

That's indeed what I meant. It is a required parameter of InStr when
used in a Jet query, unless used in the MS Access UI.

an optional parameter that
comes *before* the required parameters just doesn't
seem natural somehow!

Agreed. This kind of construct is tricky to code too!

Jamie.

--