From Abbreviation to full form street address Column E

[KAMRAN AJ]

Hi ,hope you are doing great .
For a active sheet i need a Dynamic range Excel Vba code.
I want to put my abbreviations in full form.
if My words are inside a row or column it works also there.
Abbreaviation Full form
Rd. Road
St. Street
Ln. Lane
E East
W West
N North
if you have any query related to it please message me i will help you.
God bless you.

[June7]

Could would need to perform recursive Replace operation. Need hard-coded substitution pairs or loop through recordset.

It would not be simple nor can it be guaranteed accurate. How would code deal with address like 222 St. John's St.?

[Paul_Hossler]

This will do what you asked, but as June7 says it's not perfect

I don't know what Dynamic range Excel Vba code. exactly means so I just used what ever cells were selected



Option Explicit


Sub ReplaceAbbr()
Call pvtReplace("rd.", "Road")
Call pvtReplace("st.", "Street")
Call pvtReplace("ln.", "Lane")
Call pvtReplace("E ", "East ")
Call pvtReplace("W ", "West ")
Call pvtReplace("N ", "North")
End Sub


Private Sub pvtReplace(Abbr As String, Repl As String)
Selection.Replace What:=Abbr, Replacement:=Repl, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

[KAMRAN AJ]

Can I attach a link youtube recorded video .from there you can easily see my instructions

[June7]

Might not be able to post clickable links until you have a certain number of posts (spam prevention).

You can post it as non-clickable - exclude http and/or www prefix or insert spaces: w w w.

[Aussiebear]

Just guessing here


Private Sub ExpandAbbreviation()
Dim lr as Long
Dim dRng as Range
lr = Cells(Rows.Count,"E").End(xlUp).Row
Set dRng = Range("E2:E" & lr)
For each cell in dRng
Call pvtReplace("rd.","Road")
Call pvtReplace("st.","Street")
Call pvtReplace("ln.", "Lane")
Call pvtReplace("Blvd", "Boulevard")
Call pvtReplace("E.", "East")
Call pvtReplace("N.", "North")
Call pvtReplace("W.", "West")
Call pvtReplace("S.", "South")
Next
End Sub

Private Sub pvtReplace(Abbr as String, Real as String)
Selection.Replace What:=Abbr, Replacement := Repl, Lookout := xlPart, SearchOrder := xlByRows, _
MatchCase := False, SearchFormat := False, ReplaceFormat := False, FormulaVersion := xlReplaceFormula2
End Sub


As both Paul and June7 have alluded to, this method will fail based on a number of issues. For example "NW" will not find "North West" because you haven't yet defined it. What happens if the data entry was simply "S" or "N" where the punctuation point wasn't entered? If time was on your side perhaps you could keep adding Call pvtReplace to each and every abbreviation but this could be nearly endless. It will also fail when you have abbreviations St. John for example.

[Aussiebear]

Might not be able to post clickable links until you have a certain number of posts (spam prevention).

The administrator has a predetermined number of posts set before you can post links. Not sure of the limit but it might be as low as 10 posts

[Paul_Hossler]

Can I attach a link youtube recorded video .from there you can easily see my instructions

It would be better if you attached a small sample workbook and clearly described what you were looking to do

For example, "Dynamic Range" could have many meanings

[georgiboy]

If you have Excel 365 then you could also use a formula that would look up a list for the changes - this would allow you to keep the lookup list dynamic and add/ remove items. See below and attached.

=LET(rng,A2:A32,f,H2:H26,r,I2:I26,MAP(rng,LAMBDA(x,LET(t,TEXTSPLIT(x," ",,TRUE),TEXTJOIN(" ",TRUE,IFNA(XLOOKUP(t,f,r),t))))))