My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Changing All caps to proper capitalization
|
|
 Apr 2 2004, 09:49 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 176 From: Rockford, IL  |
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. |
 |
 
|
|
Apr 2 2004, 10:00 AM
Post
#2
|
|
|
UtterAccess Guru Posts: 646 From: Central Illinois |
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.
|
|
|
|
|
Apr 2 2004, 10:07 AM
Post
#3
|
|
|
UtterAccess Addict Posts: 176 From: Rockford, IL |
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? |
|
|
|
|
Apr 2 2004, 10:37 AM
Post
#4
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
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 |
|
|
|
|
Apr 2 2004, 10:52 AM
Post
#5
|
|
|
UtterAccess Addict Posts: 176 From: Rockford, IL |
Thank you...it worked awesome!!!
I just needed to change the cell to the one that I was using. |
|
|
|
|
Apr 2 2004, 10:56 AM
Post
#6
|
|
|
UtterAccess VIP Posts: 3,656 From: Yorkshire, England. |
Glad to help - have a good weekend everybody I'm nipping off home now!
Ad |
|
|
|
|
Apr 2 2004, 11:06 AM
Post
#7
|
|
|
UtterAccess Guru Posts: 646 From: Central Illinois |
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.
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th June 2013 - 09:39 AM |