OK, I know this has to be possible, but I'm lost as to how to do it.

I have a column that holds both a city and a state (e.g. "City, State"). The problem is that when I copy and paste this information from where I have to, the information is in all caps. I need to format this column so that the First letter of the city is Capitlized, the rest of the city is in lowercase, and the state is in caps (eg. "Denver, CO").

Keep in mind that I currently have both the city and state in the same cell, so when I use the PROPER function, it makes the first letter of the state in caps and the second in lowercase (eg. "Co").

Any ideas? Will I have to seperate the city from the state? If so, how do I do that? Thanks for any help.

Scott...you should be able to use the Right() function to specify the state characters , then use the Upper() function to force them to uppercase.

I'm not familiar with using those functions in a single cell (with multiple functions in it). Can you give me a tad more instruction? *I'm sorry*

Will I need to use the PROPER function in addition to the right funtion. And how do I do the right function on the states only?

Hi,

Use the following function:

=PROPER(MID(A1,1,LEN(A1)-2))&UPPER(RIGHT(A1,2))

This assumes that your value is in cell 1, and the state is ALWAYS the last two letters in the cell.

HTH,

Ad

Thank you...it worked awesome!!!

I just needed to change the cell to the one that I was using.

Glad to help - have a good weekend everybody I'm nipping off home now!

Ad

Sorry it took so long to get back. Got called away to bosses office. Looks like Ad has taken care of you though. Glad you got it working.