Reversing names from last, first

  • Thread starter Thread starter Burt
  • Start date Start date
B

Burt

I am using Office 2000 on a PC. I have a list of names, addresses, email
addresses, etc, on an excel spreadsheet. The names are listed as "last,
first" in a single column titled "name". I would like to revise the name
list to read "first (space) last" for mailings. Is there a function that
can parse the character string and accomplish this, or is there a formula
that can recognize the position of the comma and parse the character strings
before and after the comma. I would like to do this on a global basis for
the entire list. I did this sort of text management years ago when I did
extensive dBase programming, but I am absolutely a newby when dealing with
excel formulas. I hope that there is a not too complex way to do this as I
dread having to do each name with a cut and paste.
 
Look here:

http://www.cpearson.com/excel/FirstLast.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am using Office 2000 on a PC. I have a list of names, addresses, email
| addresses, etc, on an excel spreadsheet. The names are listed as "last,
| first" in a single column titled "name". I would like to revise the name
| list to read "first (space) last" for mailings. Is there a function that
| can parse the character string and accomplish this, or is there a formula
| that can recognize the position of the comma and parse the character strings
| before and after the comma. I would like to do this on a global basis for
| the entire list. I did this sort of text management years ago when I did
| extensive dBase programming, but I am absolutely a newby when dealing with
| excel formulas. I hope that there is a not too complex way to do this as I
| dread having to do each name with a cut and paste.
|
|
 
Hi Burt,

With your data in A1 down
put this in B1 and drag down

=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1)

This assumes that your data is all like
Smith, Bill
St James, Peter
Fothington-Smythe III, Winston
etc.

In other words always a comma and space delimiter, if that is not the
case please post the full range of variables you need to deal with.

HTH
Martin
 
Re-reading your post there is probably no space in there,
so take out the first -1,

e.g.
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1)
becomes
=RIGHT(A1,LEN(A1)-FIND(",",A1))&" "&LEFT(A1,FIND(",",A1)-1)

HTH
Martin
 
Assuming there is a space after that comma between "last" and "first"...

=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)-1)

Rick
 
Martin - there is a comma and a space. I should have been more clear. I'll
keep this command sequence for reference as well. Thanks again.
Burt
 
Rick - another concise, simple approach, which I appreciate. Thanks for
your response.

Burt
 
Burt said:
I am using Office 2000 on a PC. I have a list of names, addresses, email
addresses, etc, on an excel spreadsheet. The names are listed as "last,
first" in a single column titled "name". I would like to revise the name
list to read "first (space) last" for mailings. Is there a function that
can parse the character string and accomplish this, or is there a formula
that can recognize the position of the comma and parse the character
strings before and after the comma. I would like to do this on a global
basis for the entire list. I did this sort of text management years ago
when I did extensive dBase programming, but I am absolutely a newby when
dealing with excel formulas. I hope that there is a not too complex way to
do this as I dread having to do each name with a cut and paste.
Click to expand...
Having gone onto many newsgroup sites to seek help with various programs
over the years, I must say that my experience of one question answered here
is as positive as any site I've ever been on. I wasn't flamed, told that my
question was stupid, or directed to try google. Many thanks to kind,
competent, helpful people who answered my question. I also appreciated
seeing several ways to solve the problem. I'm reminded of the time in the
early 1980's when a friend and I started on a quest to be self-taught in
dBase. We wrote similar programs for office data, communication, and
financial management and came up with very similar results after many
thousands of lines of code. Although the results were basically the same,
the code was so much different that we would have had difficulty debugging
each other's programs. I'm sure that this experience exists throughout the
programming arena. To a non-professional, this aspect of computerization is
intriguing, to say the least.

Burt
 
Martin - The list is a download from our county tax department. Individuals
on the list had the usual "comma, space" delimiter for lastname, firstname
middle initial. What I hadn't counted on, however, was the presence of
about 15 to 20% of entries that were either corporations or trusts that were
in corrrect order with no commas. Since I hadn't thought about the
exceptions causing a problem I didn't mention them to you. They, of
course, gave an error message. Since I want to use this list for a mailer,
without the need for a salutation or address line, I had to alter your code
to restate the original text from col A to col B where there had been no
commas as well as reverse the comma-space delimited entries and put them in
col B.

The aftermarket Excel 2000 "Bible" (Walkenbach) doesn't have the best
description of the functions, their syntax, and what they create, given the
context. When I did self taught dBase programming I had several books, but
the absolute best one had a complete list of functions with extensive
description of their use and syntax. I am not planning to become proficient
in Excel - I just need to solve a few simple problems as they come up. By
trial and error I came up with the following code that did what I needed.
It did take quite a long time! As a reference I used the link that another
responder to my post suggested.

IF(ISERROR(FIND(",",A1:A100,1)),A1:A100,(RIGHT(A1:A100,LEN(A1:A100)-FIND(",",A1:A100)-1)&"
"&LEFT(A1:A100,FIND(",",A1:A100)-1)))

There are still a few issues due to the way the trust names were listed, but
that brings the amount of editing of the original entries down to a very
small percentage. Fortunately, name suffixes were shown without commas!

Thanks, again, to those who responded.

Burt
 
Hi Burt,

Glad you finally worked it out. These sort of transitions are always
difficult and rather kludgy in Excel, mostly due to the great variety
of the original data. Often the only solution is to use multiple helper
columns, utilising staged stripping of the data and applying some
creative lateral thinking to the use of various functions.

The most used functions for this process are RIGHT, LEFT, MID,
FIND, SEARCH and SUBSTITUTE. You can find a very practical
explanation of these and many others at the following site.
http://www.xlfdic.com/
The downloaded file will require you to enable macros, there is
no need to be concerned about viruses. Myself and many others
in these newsgroups can vouch for the sites integrity. I think
you will find this to be as helpful as the dBase book you mentioned.

Another great site with lots of practical, easy to follow demos
is Debra Dalgleish's Contextures.
http://www.contextures.com/tiptech.html

Also as you have already noted these groups are not like the
other newsgroups, often when people post questions saying
something like "i've been working on this for hours/days etc..."
the reply will be something like "Why did you waste so much time,
you should have posted here First.". These groups are full of
people who enjoy the challenge of solving a juicy new problem.
Makes a refreshing change from other groups.

I hope all goes well for you and don't be afraid to post any questions
to the groups, there are a lot of very smart people here, particularly
in the lateral thinking part, a lot of very complex problems find
very simple solutions around these parts.

This group is quite good however the General Questions group
and Worksheet Functions seem to get more traffic. Not that
it really matters, most people monitor all of the groups.

Regards
Martin
 
Martin - thanks for the great links. Now I have to do my homework.
Starting with my first IBM PC in the early 80's, I've been able to find much
better solutions to my software problems in forums and usergroups than on
the vendor's tech support sites or tech support by phone. I do try to work
through problems myself before asking for help - must be the "guy" thing
like not asking directions when lost!

I've participated on the comp.periphs.printers for several years. A fellow
on that newsgroup named Arthur Entlich helped me to get an Epson inkjet
printer unclogged and I've stayed on it ever since to help newbies when I
could. Unfortunately, there are a few obnoxious posters, one of whom is the
resident, forever present, spewer-of-misinformation troll. He's virtually
hijacked the NG. Also raised repeatedly by a few participants is the top
vs. bottom posting argument. Top posting makes sense to me, and I
appreciate that this NG approves of top posting.

A better site for inkjet printer maintenance and aftermarket ink/carts info
is the Nifty-stuff forum. It is moderated and is very successful in keeping
jerks away as well as deleting commercial or antagonistic posts.

Burt
 
Back
Top