Looking for formula that will make some characters lower case and some characters upper case in excel

Question

I am trying to make TAMPA, FL into Tampa, FL, but I don't know the correct formula to do this. Any ideas?

Answer 1

If you have Office 365, try:

=TEXTJOIN(",",TRUE,PROPER(TEXTBEFORE(A1,",")),UPPER(TEXTAFTER(A1,",")))

Answer 2

This is a bit long for a comment, though not an exact answer -- it would be if someone converted the VBA metacode as in this StackOverflow example to something useful:

1. Use INSTR(RIGHT(line, 4), ", "), verify that the text in the line ends in comma + space + exactly two-character state (and province? territory?) postal abbreviation. (This presumes that two characters are always used, which is not true for Mexican states and other countries.) As part of verification, you might check that the two-character postal abbreviation is in a list.
2. Split the cell with City, ST into two cells, possibly omitting the comma + space entirely (to be added back in later).
3. Use PROPER() to fix City case (which will also correct multi-word cities, e.g., New York City.
4. Use UPPER() to fix state abbreviations.
5. This is a useful way to store the data, and may make postal code lookup[ easier.