Kamranas
Banned User
- Joined
- Feb 25, 2023
- Messages
- 53
- Office Version
- 2016
- Platform
- Windows
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
not working fo me thisHi 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 rowHi 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 exampleHi 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