Street abbreviations

Status
Not open for further replies.

Kamranas

Banned User
Joined
Feb 25, 2023
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
I need a dynamic excel VBA code that puts my abbreviation in full form.For column E.
Ave. Avenue
S South
E East
W West
N North
hwy Highway
Rd Road
STE Suite
N.E. Northeast
S.E. Southeast
Blvd Boulevard
St Street
 

Attachments

  • ABBREE.PNG
    ABBREE.PNG
    18.1 KB · Views: 16

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi Kamranas,

Here is a code that should get you what you are looking for. You can use VBA's Replace function to replace each abbreviation with its corresponding full text. To avoid mistakenly converting text strings in the middle of a word, you can use the InStr function to check whether the abbreviation occurs at the beginning of a word (i.e., whether it is preceded by a space or the start of the string).

VBA Code:
Sub ConvertAddresses()
    Dim cell As Range
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        ' Check for each abbreviation at beginning of word
        If InStr(1, cell.Value, " Ave.") = 1 Then
            cell.Value = Replace(cell.Value, " Ave.", " Avenue")
        End If
        If InStr(1, cell.Value, " S ") = 1 Then
            cell.Value = Replace(cell.Value, " S ", " South ")
        End If
        If InStr(1, cell.Value, " E ") = 1 Then
            cell.Value = Replace(cell.Value, " E ", " East ")
        End If
        If InStr(1, cell.Value, " W ") = 1 Then
            cell.Value = Replace(cell.Value, " W ", " West ")
        End If
        If InStr(1, cell.Value, " N ") = 1 Then
            cell.Value = Replace(cell.Value, " N ", " North ")
        End If
        If InStr(1, cell.Value, "hwy") = 1 Then
            cell.Value = Replace(cell.Value, "hwy", "Highway")
        End If
        If InStr(1, cell.Value, " Rd") = 1 Then
            cell.Value = Replace(cell.Value, " Rd", " Road")
        End If
        If InStr(1, cell.Value, "STE") = 1 Then
            cell.Value = Replace(cell.Value, "STE", "Suite")
        End If
        If InStr(1, cell.Value, " N.E.") = 1 Then
            cell.Value = Replace(cell.Value, " N.E.", " Northeast")
        End If
        If InStr(1, cell.Value, " S.E.") = 1 Then
            cell.Value = Replace(cell.Value, " S.E.", " Southeast")
        End If
        If InStr(1, cell.Value, " Blvd") = 1 Then
            cell.Value = Replace(cell.Value, " Blvd", " Boulevard")
        End If
        If InStr(1, cell.Value, " St") = 1 Then
            cell.Value = Replace(cell.Value, " St", " Street")
        End If
    Next cell
End Sub

Let me know if you need any further help!

... Mike
 
Upvote 0
Hi Kamranas,

Here is a code that should get you what you are looking for. You can use VBA's Replace function to replace each abbreviation with its corresponding full text. To avoid mistakenly converting text strings in the middle of a word, you can use the InStr function to check whether the abbreviation occurs at the beginning of a word (i.e., whether it is preceded by a space or the start of the string).

VBA Code:
Sub ConvertAddresses()
    Dim cell As Range
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        ' Check for each abbreviation at beginning of word
        If InStr(1, cell.Value, " Ave.") = 1 Then
            cell.Value = Replace(cell.Value, " Ave.", " Avenue")
        End If
        If InStr(1, cell.Value, " S ") = 1 Then
            cell.Value = Replace(cell.Value, " S ", " South ")
        End If
        If InStr(1, cell.Value, " E ") = 1 Then
            cell.Value = Replace(cell.Value, " E ", " East ")
        End If
        If InStr(1, cell.Value, " W ") = 1 Then
            cell.Value = Replace(cell.Value, " W ", " West ")
        End If
        If InStr(1, cell.Value, " N ") = 1 Then
            cell.Value = Replace(cell.Value, " N ", " North ")
        End If
        If InStr(1, cell.Value, "hwy") = 1 Then
            cell.Value = Replace(cell.Value, "hwy", "Highway")
        End If
        If InStr(1, cell.Value, " Rd") = 1 Then
            cell.Value = Replace(cell.Value, " Rd", " Road")
        End If
        If InStr(1, cell.Value, "STE") = 1 Then
            cell.Value = Replace(cell.Value, "STE", "Suite")
        End If
        If InStr(1, cell.Value, " N.E.") = 1 Then
            cell.Value = Replace(cell.Value, " N.E.", " Northeast")
        End If
        If InStr(1, cell.Value, " S.E.") = 1 Then
            cell.Value = Replace(cell.Value, " S.E.", " Southeast")
        End If
        If InStr(1, cell.Value, " Blvd") = 1 Then
            cell.Value = Replace(cell.Value, " Blvd", " Boulevard")
        End If
        If InStr(1, cell.Value, " St") = 1 Then
            cell.Value = Replace(cell.Value, " St", " Street")
        End If
    Next cell
End Sub

Let me know if you need any further help!

... Mike
not working fo me this
 
Upvote 0
Could you post an example of your worksheet with Xl2bb?
 
Upvote 0
Other than >0, a few issues can still arise. For example, if a street is called "St. Thomas". That might be resolved by using " St " rather than " St".
 
Upvote 0
Hi Kamranas,

Here is a code that should get you what you are looking for. You can use VBA's Replace function to replace each abbreviation with its corresponding full text. To avoid mistakenly converting text strings in the middle of a word, you can use the InStr function to check whether the abbreviation occurs at the beginning of a word (i.e., whether it is preceded by a space or the start of the string).

VBA Code:
Sub ConvertAddresses()
    Dim cell As Range
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        ' Check for each abbreviation at beginning of word
        If InStr(1, cell.Value, " Ave.") = 1 Then
            cell.Value = Replace(cell.Value, " Ave.", " Avenue")
        End If
        If InStr(1, cell.Value, " S ") = 1 Then
            cell.Value = Replace(cell.Value, " S ", " South ")
        End If
        If InStr(1, cell.Value, " E ") = 1 Then
            cell.Value = Replace(cell.Value, " E ", " East ")
        End If
        If InStr(1, cell.Value, " W ") = 1 Then
            cell.Value = Replace(cell.Value, " W ", " West ")
        End If
        If InStr(1, cell.Value, " N ") = 1 Then
            cell.Value = Replace(cell.Value, " N ", " North ")
        End If
        If InStr(1, cell.Value, "hwy") = 1 Then
            cell.Value = Replace(cell.Value, "hwy", "Highway")
        End If
        If InStr(1, cell.Value, " Rd") = 1 Then
            cell.Value = Replace(cell.Value, " Rd", " Road")
        End If
        If InStr(1, cell.Value, "STE") = 1 Then
            cell.Value = Replace(cell.Value, "STE", "Suite")
        End If
        If InStr(1, cell.Value, " N.E.") = 1 Then
            cell.Value = Replace(cell.Value, " N.E.", " Northeast")
        End If
        If InStr(1, cell.Value, " S.E.") = 1 Then
            cell.Value = Replace(cell.Value, " S.E.", " Southeast")
        End If
        If InStr(1, cell.Value, " Blvd") = 1 Then
            cell.Value = Replace(cell.Value, " Blvd", " Boulevard")
        End If
        If InStr(1, cell.Value, " St") = 1 Then
            cell.Value = Replace(cell.Value, " St", " Street")
        End If
    Next cell
End Sub

Let me know if you need any further help!

... Mike
you make this code for an entire row it is not work inside a row
 

Attachments

  • Not working.PNG
    Not working.PNG
    22.8 KB · Views: 14
Upvote 0
Hi Kamranas,

Here is a code that should get you what you are looking for. You can use VBA's Replace function to replace each abbreviation with its corresponding full text. To avoid mistakenly converting text strings in the middle of a word, you can use the InStr function to check whether the abbreviation occurs at the beginning of a word (i.e., whether it is preceded by a space or the start of the string).

VBA Code:
Sub ConvertAddresses()
    Dim cell As Range
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        ' Check for each abbreviation at beginning of word
        If InStr(1, cell.Value, " Ave.") = 1 Then
            cell.Value = Replace(cell.Value, " Ave.", " Avenue")
        End If
        If InStr(1, cell.Value, " S ") = 1 Then
            cell.Value = Replace(cell.Value, " S ", " South ")
        End If
        If InStr(1, cell.Value, " E ") = 1 Then
            cell.Value = Replace(cell.Value, " E ", " East ")
        End If
        If InStr(1, cell.Value, " W ") = 1 Then
            cell.Value = Replace(cell.Value, " W ", " West ")
        End If
        If InStr(1, cell.Value, " N ") = 1 Then
            cell.Value = Replace(cell.Value, " N ", " North ")
        End If
        If InStr(1, cell.Value, "hwy") = 1 Then
            cell.Value = Replace(cell.Value, "hwy", "Highway")
        End If
        If InStr(1, cell.Value, " Rd") = 1 Then
            cell.Value = Replace(cell.Value, " Rd", " Road")
        End If
        If InStr(1, cell.Value, "STE") = 1 Then
            cell.Value = Replace(cell.Value, "STE", "Suite")
        End If
        If InStr(1, cell.Value, " N.E.") = 1 Then
            cell.Value = Replace(cell.Value, " N.E.", " Northeast")
        End If
        If InStr(1, cell.Value, " S.E.") = 1 Then
            cell.Value = Replace(cell.Value, " S.E.", " Southeast")
        End If
        If InStr(1, cell.Value, " Blvd") = 1 Then
            cell.Value = Replace(cell.Value, " Blvd", " Boulevard")
        End If
        If InStr(1, cell.Value, " St") = 1 Then
            cell.Value = Replace(cell.Value, " St", " Street")
        End If
    Next cell
End Sub

Let me know if you need any further help!

... Mike
it is not working for me because if my words are inside a row,this code will not work there for example
you can see a screenshot
 

Attachments

  • words inside a row.PNG
    words inside a row.PNG
    27.2 KB · Views: 8
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top