Easy way to capitalize state abbreviations in a cell

[Mikec1012]

Hello all. Working with an in-depth spreadsheet. I have one cell that contain CITY STATE ZIP and I canny split. I’m running a macro that will proper case my cells. However, on the cell with City State Zip - my state abbreviation goes from FL to Fl. Is there a macro I can run again to change the state abbreviation back to both caps? I’m doing the find/replace but there has to be an easier way. Thank you all.

 

[sykes]

Hi, and welcome to Mr. Excel!!

Can't you just incorporate UCase into your code, somewhere? EG:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("D1") = UCase(Me.Range("C1").Value)
End Sub

My example simply fires off the worksheet_change event, and converts anything in C1, to uppercase, in D1.
Looking at what you've done already, I'm guessing that you could just write an extra line or two into your extant code, using UCase to alter the State part of your string.

 

[Rick Rothstein]

You could have told us what column your city/state/zips were in and whether there was a header or not. Okay, here is a generalized macro that you will have to set the first cell with data's address for the StartCell variable (see red highlighted text below).

[table="width: 500"]
[tr]
	[td]Sub UpperCaseStateAbbreviations()
  Dim LastRow As Long, StartCell As Range
  Set StartCell = Range("[B][COLOR="#FF0000"]E2[/COLOR][/B]")
  LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
  With Range(StartCell, Cells(LastRow, StartCell.Column))
    .Value = Evaluate(Replace("IF(@="""","""",REPLACE(@,LEN(@)-6,1,UPPER(MID(@,LEN(@)-6,1))))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[sykes]

Anyone out there? Were either of our responses useful?