Excel 2007 : Suffix formula needed for the Jr, Sr, III in Excel 2007

[Lostinexcel2002]

Hello, I need help fixing the following code:

=IF(COUNT(SEARCH(" ",M1),SEARCH(" ", M1, SEARCH(" ",M1)+1))>1,PROPER(LEFT(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1)))&PROPER(MID(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1),99)),PROPER(M1))

The result of the code above gives me: Kent C & Cathy Honda Iii

I would like for it to show Kent C & Cathy Honda III

Thanks

[teylyn]

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))

[Lostinexcel2002]

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!

[teylyn]

To answer that I'd really have to see the setup of the worksheet and not just the formula for one cell.

[Lostinexcel2002]

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:

=IF(COUNT(SEARCH(" ",M1),SEARCH(" ", M1, SEARCH(" ",M1)+1))>1,PROPER(LEFT(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1)))&UPPER(MID(M1,SEARCH(" ", M1, SEARCH(" ",M1)+1),99)),PROPER(M1))

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:

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

[shg]

How about =substitute(substitute(substitute(allThat, " iii", " III), " JR", " Jr"), " SR", " Sr")

[Lostinexcel2002]

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

[teylyn]

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

[Paul]

Recently cross-posted at MrExcel.

http://www.mrexcel.com/forum/showthread.php?t=415393