Find & Replace Multiple State abbreviations
 

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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

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

"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

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

"Maverick50" wrote:

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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

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 (MVP - Excel)


"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

One Column

"Don Guillett" wrote:

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

"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

Then use Mike's

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Maverick50" wrote in message
...
One Column

"Don Guillett" wrote:

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

"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone:
9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone:
9898332265

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

Find & Replace Multiple State abbreviations
 

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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone: 9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone: 9898332265

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

Find & Replace Multiple State abbreviations
 

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.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Then use Mike's

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Maverick50" wrote in message
...
One Column

"Don Guillett" wrote:

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

"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone:
9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone:
9898332265

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

Find & Replace Multiple State abbreviations
 

I didn't, but should have, tested.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Then use Mike's

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Maverick50" wrote in message
...
One Column

"Don Guillett" wrote:

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

"Maverick50" wrote in message
...
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<brLighthouse Point FL 33064<brPhone:
9544809085
11 West 2ND Avenue<br<brWilliamson WV 25661<brPhone: 3042355659
10673 W Street<br Charles Road<brSumner MI 48889<brPhone:
9898332265

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