string manipulation

[Steven Deng]

Hello, I have a list of names in the form of "Firstname optionalNames
LastName", and I would like to convert them to the form of "*Lastname,
Firstname optionalNames"

e.g.
"John Doe" is converted to "*Doe, John"

I have wrriten some vba codes (about 20 lines) which can do this. However, a
friend told me that this can be done using a sequence of built-in functions.
My programming background is mosty C/C++, where I usually have to write
everything from scratch. As I am not too familiar with the vast built-in
functions available, I am wondering if how this is done.

Sincerely,
Steven Deng

 

[David McRitchie]

Hi Steven,
Nothing builtin, but then that is why you are in excel.programming
isn't it. If not see my getstarted.htm page for installing a macro.

For solutions you might look at firstname and lastname macros
on my page
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#lastname
and look at Chip Pearson's page
Extracting First And Last Names
http://www.cpearson.com/excel/FirstLast.htm

You will probably have to insert a character like underscore
before converting and removing it after converting such names
as van Pelt, van der Beck, Di Matteo

Since you are working with names you might also have to change
capitalizations with names that the PROPER worksheet function
and macros based on it would simply mess up.
http://www.mvps.org/dmcritchie/excel/proper.htm#proper

 

[Bob Phillips]

Steven,

Try this worksheet formula

=MID(A1,FIND("~",(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))+1,99)&" "&LEFT(A1,FIND("~",(SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steven Deng]

Thank you david,

Actually, I wrote a macro and solved that problem a couple days ago using
functions like (strconv, split, & ...etc). A friend told me that someone
showed just combining these function in some order and then solved the
problem within one line of code (which can be applied without invoking
macros)

The reason I post this is to that i want to see how elegant that solution
can be.

Sincerely,
Steven Deng.

 

[Guest]

Steven,

Could you skip the programming altogether and use the "Text to Columns"
function (delimited by spaces) to separate the names into different columns,
then cut and paste the 'last name' column to go first? You could concatenate
them back together afterward.

Eric

 

[Steven Deng]

Sweet ! Bob, Now I need to do is to modify it a little bit to get "*" and
"," in place!

Sincerely
Steven Deng

 

[Steven Deng]

Eric,

Thank you for the reply! Since I have about 3000 names, and the number of
words in a name varys, this approach may end up with lots of manual editing.

Sincerely,
Steven Deng

 

[David McRitchie]

Hi Steven,
The advantage of a macro is ease of use, and not having
to maintain an original column and the column you want.
If you have lastnames comprised of more than one word
and you want ease of use you will have to use a macro.

No matter what you do, it always comes down to knowing
your own data, and of course the results you want, like that
asterisk.

 

[Bob Phillips]

Are you okay with that, or do you want help?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steven Deng]

Ya ! Bob, Here is how it may look like.


="*" & MID(A1,FIND("~",(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))+1,99)&", "&LEFT(A1,FIND("~",(SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Also I am wondering, how can I apply this piece of function
on a list of cells instead of one cell (say the names are in the cells
A1:A3000), How can I achieve this without invoking writing macros?

Sincerely,
Steven Deng

 

[Bob Phillips]

Steven,

Just copy that select, then select all the others and past the formula in.
Excel will adjust to the appropriate rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Ya ! Bob, Here is how it may look like.


="*" & MID(A1,FIND("~",(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))+1,99)&", "&LEFT(A1,FIND("~",(SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Also I am wondering, how can I apply this piece of function
on a list of cells instead of one cell (say the names are in the cells
A1:A3000), How can I achieve this without invoking writing macros?

Sincerely,
Steven Deng