Street abbreviations

Status
Not open for further replies.
Kamranas

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: 13

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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
mikenelena said:
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
Click to expand...
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
mikenelena said:
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
Click to expand...
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: 11
Upvote 0
mikenelena said:
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
Click to expand...
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: 5
Upvote 0
Status
Not open for further replies.

Similar threads

Kamranas
  • Locked
  • Question
Address Abbreviations
Replies
1
Views
456
Fluff
Fluff
B
  • Question
Text to column with uneven delimiters and spacing
Replies
3
Views
490
kweaver
kweaver
B
  • Question
Reference UK Post Code Column to Create and print Country from a reference table? IF formula or VLookup?
Replies
5
Views
334
Fluff
Fluff
B
  • Solved
Fix Address field
Replies
5
Views
406
Micron
M
S
  • Question
Needed a formula to convert an address from all upper case to normal address format
Replies
4
Views
759
Stumpped
S

Forum statistics

Threads
1,215,083
Messages
6,123,020
Members
449,092
Latest member
ikke

Share this page

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