Find & Replace Multiple State abbreviations

[Maverick50]

I have multiple State abbreviations with no comma. How do I add the comma
with all the state abbreviations? I have been doing it separately…very time
consuming.
Find what: WY
Replace with: , WY

Actual
4915 N Federal Highway<br>Lighthouse Point FL 33064<br>Phone: 9544809085
11 West 2ND Avenue<br><br>Williamson WV 25661<br>Phone: 3042355659
10673 W Street<br> Charles Road<br>Sumner MI 48889<br>Phone: 9898332265

Needed Results
4915 N Federal Highway<br>Lighthouse Point, FL 33064<br>Phone: 954-480-9085
11 West 2ND Avenue<br><br>Williamson, WV 25661<br>Phone: 304-235-5659
10673 W Street<br> Charles Road<br>Sumner, MI 48889<br>Phone: 989-833-2265

 

[Don Guillett]

Is it all in ONE column or in 3?
If in columns just use left and , and right len-8 for 2 char st and 5 char
zip.
if all in one column use select case for states and instrREV
??

 

[Mike H]

Hi,

I've assumed column A. Right click your sheet tab, view code and paste this in

Add the state abbreviations to the array being careful to maintain the
syntax and run the code. Note that the comparison where it looks for the
state code is case sensitive

Sub Sonic()
Dim c As Range, MyRange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
myarray = Array("FL", "WV", "MI")
For Each c In MyRange
For x = 0 To UBound(myarray)
If InStr(c, myarray(x)) > 0 Then
If (Right(Trim(Left(c, InStr(c, myarray(x)) - 1)), 1)) <> "," Then
c.Value = Trim(Left(c, InStr(c, myarray(x)) - 1)) _
& ", " & Mid(c, InStr(c, myarray(x)))
End If
End If
Next
Next
End Sub

Mike

 

[Rick Rothstein]

Give this macro a try (replace my example assignments in the Const
statements with assignments representing your actual layout)...

Sub FixStateAbbreviation()
Dim X As Long
Dim LastRow As Long
Dim Fields() As String
Dim DataLines() As String

Const DataStartRow As Long = 1
Const DataColumn As String = "A"
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To LastRow
DataLines = Split(.Cells(X, DataColumn).Value, vbLf)
Fields = Split(DataLines(UBound(DataLines) - 1), " ")
Fields(UBound(Fields) - 2) = Fields(UBound(Fields) - 2) & ","
DataLines(UBound(DataLines) - 1) = Join(Fields, " ")
.Cells(X, DataColumn) = Join(DataLines, vbLf)
Next
End With
End Sub

 

[Rick Rothstein]

I don't know whether the double line break (<br><br>) that you showed on the
second example address was intentional or not, but my code *will*
successfully handle it. However, the reason I am writing this follow up
message is to mention that if a double line break occurs *after* the zip
code line, then my code will not handle it. If this situation could occur,
then let me know and I'll modify the code to account for that possibility.

 

[Maverick50]

One Column

Is it all in ONE column or in 3?
If in columns just use left and , and right len-8 for 2 char st and 5 char
zip.
if all in one column use select case for states and instrREV
??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Then use Mike's

 

[Rick Rothstein]

Whoops! I missed the Phone Number part. This should do both things you
wanted...

Sub FixStateAbbreviationAndPhoneNumbers()
Dim X As Long
Dim LastRow As Long
Dim Fields() As String
Dim DataLines() As String

Const DataStartRow As Long = 1
Const DataColumn As String = "A"
Const SheetName As String = "Sheet4"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To LastRow
DataLines = Split(.Cells(X, DataColumn).Value, vbLf)
Fields = Split(DataLines(UBound(DataLines) - 1), " ")
Fields(UBound(Fields) - 2) = Fields(UBound(Fields) - 2) & ","
DataLines(UBound(DataLines) - 1) = Join(Fields, " ")
DataLines(UBound(DataLines)) = Format(DataLines(UBound(DataLines)), _
"""Phone: ""!@@@-@@@-@@@@")
.Cells(X, DataColumn) = Join(DataLines, vbLf)
Next
End With
End Sub

 

[Rick Rothstein]

You didn't like my macro? :-(

Only kidding. <g>

Actually, though, both Mike's and my macros didn't handle the phone number
reformat the that the OP also wanted (see the "Needed Results" from his
sample data). I just posted a modification to my macro which handles the
comma and the phone number... I presume Mike will follow up with a
modification to his own macro.

 

[Don Guillett]

I didn't, but should have, tested.