LinkBack URL
About LinkBacks
Hello, I need help fixing the following code:The result of the code above gives me: Kent C & Cathy Honda Iii
Please Login or Register to view this content.
I would like for it to show Kent C & Cathy Honda III
Thanks
Hello, I need help fixing the following code:The result of the code above gives me: Kent C & Cathy Honda IiiPlease Login or Register to view this content.
I would like for it to show Kent C & Cathy Honda III
Thanks
Last edited by Lostinexcel2002; 09-11-2009 at 01:47 AM.
Hi,
PROPER will convert any string to uppercase first letter and lowercase rest of string.
If you want all Caps, then user
=IF(COUNT(SEARCH(" ",M1),SEARCH(" ", M1, SEARCH(" ",M1)+1))>1,UPPER(LEFT(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1)))&UPPER(MID(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1),99)),UPPER(M1))
Hello Teylen,
Thanks for your reply, since I am formulating to a letter, I would really rather have the name display as proper as I have indicated in my initial question. Is there anyway to accomplish that?
Again, I would like for the format to show exactly: Kent C & Cathy Honda III
Thanks again!
To answer that I'd really have to see the setup of the worksheet and not just the formula for one cell.
Ok, I have data populating over to a .csv spreadsheet from a client database that contains the sales report. The report appears like this:
Last name: Honda III
First name: KENT C & CATHY
Note that the sales report defaults to all caps.
I have a macro that will populate the information from the sales report to the .csv worksheet and a formula to populate from the CSV form to a letter constructed in excel 2007 that I want to change the name from all caps to Proper format with the exception of the suffix on some of the names such as III, Jr, Sr, etc.
I tried the following formula below:
The result is pulling from cell M1 that contains all caps " KENT C & CATHY HONDA III " that is populated from the CSV form and in cell M2, the result of the formula above displays the following:Please Login or Register to view this content.
John C & CATHY HONDA III
As you can see, there is a mix of proper and upper case. I want to know how I could have the display in M2 to display exactly as:
Kent C & Cathy Honda III
I hope this helps...Thanks
How about =substitute(substitute(substitute(allThat, " iii", " III), " JR", " Jr"), " SR", " Sr")
Entia non sunt multiplicanda sine necessitate
Hello Shg,
How do I apply your formula to my spreadsheet? I am trying to transfer data from cell M1 to M2. Do I paste your formula in M2? How would it know to populate from M1? Sorry as I am still learning on excel 2007
Thanks
You can't. There are just too many possible variations of names to come up with a rule to decide if the last word in a text string should be all caps or not.
It's easy to find a way to capitalise the last word in every instance, but then you'll end up with situations like
Kent C & Cathy Honda III
John & Mary BLOGGS
James C & Mary June HIGGINS
Barbara Taylor BRADFORD
Kim S & Su Wong II
etc.
You may want to use your formula as is and then send the spreadsheets through some Replace commands, like
replace Iii with III
replace Ii with II
Recently cross-posted at MrExcel.
http://www.mrexcel.com/forum/showthread.php?t=415393
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks