formatting a test string

[keith05281967]

Greetings,

I've got some text strings that I'm trying to format in VBA.
An example would be changing this:
"San Diego CA" to this "SAN DIEGO, CA"
"Houston TX" to this "HOUSTON, TX"

So, regardless how many spaces are between the city state, and yes it varies, I want it to be formatted to city-comma-one space-state like the above example.

The below code is close to what i'm looking for but it's but only close. It isn't intelligent enough to adjust with varying spacing.

Selection.Value = UCase(Selection.Value)
s = Cells(i, ColumnNumber1).Value
s = Trim(s)
s = Left(s, Len(s) - 9) & ", " & Right(s, 2)


I'm looking for a VBA solution, not a spreadsheet function. Any advice would be appreciated.

thank you,
Keith

 

[hiker95]

keith05281967,

For a macro solution can we have some screenshots?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Rick Rothstein]

Select all the cells you want to process and run this macro...

Sub FormatCityState()
  Dim LastSpace, Text As String, Cell As Range
  For Each Cell In Selection
    Text = UCase(WorksheetFunction.Trim(Cell))
    LastSpace = InStrRev(Text, " ")
    Cell = Left(Text, LastSpace - 1) & "," & Mid(Text, LastSpace)
  Next
End Sub

 

[keith05281967]

Hi Rick - That does everything I need it to "except" deal with the varying spaces that exist between CITY and comma. I had more spacing in my example but when i posted it, i guess the editor removed them, which is funny because that is what i'm trying to do. I inserted the dots in the below example so the editor won't remove the spacing that is present. Column B is the result i'm currently getting. What it should look like is: CENTENNIAL, CO.

<TABLE style="WIDTH: 322pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=430><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" span=2 width=215><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=17 width=215>COLUMN A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=215>COLUMN B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>Centennial..............CO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>CENTENNIAL........., CO</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>San Diego...............CA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>SAN DIEGO.............., CA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>Fremont.........................CA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>FREMONT.........................., CA</TD></TR></TBODY></TABLE>

thanks,
Keith

 

[Rick Rothstein]

Hi Rick - That does everything I need it to "except" deal with the varying spaces that exist between CITY and comma.

Does this code do what you want then...

Sub FormatCityState()
  Dim LastSpace, Text As String, Cell As Range
  For Each Cell In Selection
    Text = UCase(WorksheetFunction.Trim(Cell))
    LastSpace = InStrRev(Text, " ")
    Cell = Trim(Left(Text, LastSpace - 1)) & ", " & Trim(Mid(Text, LastSpace + 1))
  Next
End Sub

 

[keith05281967]

YES!! That does it exactly! Brilliant.

Thank you Rick.