Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Steven Deng" wrote in message news:PYPhd.96078$nl.5812@pd7tw3no... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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" wrote in message news:PYPhd.96078$nl.5812@pd7tw3no... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
Sweet ! Bob, Now I need to do is to modify it a little bit to get "*" and
"," in place! Sincerely Steven Deng "Bob Phillips" wrote in message ... 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," ","")))))) -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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 "Eric" wrote in message ... 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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. "Steven Deng" wrote in message news:TjRhd.96840$nl.33207@pd7tw3no... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
Are you okay with that, or do you want help?
-- HTH RP (remove nothere from the email address if mailing direct) "Steven Deng" wrote in message news:UYRhd.97175$nl.83171@pd7tw3no... Sweet ! Bob, Now I need to do is to modify it a little bit to get "*" and "," in place! Sincerely Steven Deng "Bob Phillips" wrote in message ... 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," ","")))))) -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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" дÈëÏûÏ¢ÐÂÎÅ ... Are you okay with that, or do you want help? -- HTH RP (remove nothere from the email address if mailing direct) "Steven Deng" wrote in message news:UYRhd.97175$nl.83171@pd7tw3no... Sweet ! Bob, Now I need to do is to modify it a little bit to get "*" and "," in place! Sincerely Steven Deng "Bob Phillips" wrote in message ... Steven, Try this worksheet formula -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
string manipulation
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) "Steven Deng" wrote in message news:p%Whd.101146$%k.9555@pd7tw2no... 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" дÈëÏûÏ¢ÐÂÎÅ ... Are you okay with that, or do you want help? -- HTH RP (remove nothere from the email address if mailing direct) "Steven Deng" wrote in message news:UYRhd.97175$nl.83171@pd7tw3no... Sweet ! Bob, Now I need to do is to modify it a little bit to get "*" and "," in place! Sincerely Steven Deng "Bob Phillips" wrote in message ... Steven, Try this worksheet formula -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String Manipulation within VBA | Excel Discussion (Misc queries) | |||
String manipulation!! | Excel Programming | |||
string manipulation | Excel Programming | |||
VBA String manipulation | Excel Programming | |||
string manipulation | Excel Programming |