VBA required to replace text only at last part of sentence

dxstudios · Sep 30th 2020

Hi Friends,

i have task to replace some particular Text in a range of cells, i have a existing code, which replaces text properly,

but now my ask is just to replace the Text only at the lastpart of the Text,

it should not replace the text in the start Text / Middle Text, it should get replaced only at the End.

Code

ActiveSheet.Range("A:A").Replace " LAKE", " LK"
ActiveSheet.Range("A:A").Replace " ROAD", " RD"
ActiveSheet.Range("A:A").Replace " STREET", " ST"
ActiveSheet.Range("A:A").Replace " DRIVE", " DR"

for example:

existing code converts like :

Lakeshore Road to LKshore RD

code required:

Lakeshore Road to Lakeshore RD

dxstudios · Oct 1st 2020

could someone please help me on this thread.

**royUK** · Oct 1st 2020

You need to attach an example workbook showing existing data and required outcome

dxstudios · Oct 1st 2020

Thank you so much for looking on my thread Mr.royUK,

i have attached the sample sheet with the existing code on it.

Book1.xlsm

**royUK** · Oct 1st 2020

Try this

Code

Option Explicit


Sub ReplaceAddress()


Dim rRng As Range, rCl As Range


Set rRng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each rCl In rRng.Cells
rCl.Value = StrReverse(Replace(StrReverse(rCl.Value), StrReverse(" Avenue"), StrReverse(" AV"), Count:=1))
rCl.Value = StrReverse(Replace(StrReverse(rCl.Value), StrReverse(" LAKE"), StrReverse(" LK"), Count:=1))
rCl.Value = StrReverse(Replace(StrReverse(rCl.Value), StrReverse(" Road"), StrReverse(" RD"), Count:=1))
rCl.Value = StrReverse(Replace(StrReverse(rCl.Value), StrReverse(" Street"), StrReverse(" ST"), Count:=1))
rCl.Value = StrReverse(Replace(StrReverse(rCl.Value), StrReverse(" Drive"), StrReverse(" DR"), Count:=1))
Next rCl
End Sub

Display More

dxstudios · Oct 1st 2020

please ignore the above thread , its working fine Sir , Thank you so much for your Help

**royUK** · Oct 1st 2020

There's something wrong with that workbook. I had to move your sheet to a new workbook

dxstudios · Oct 1st 2020

Thank you so much for this valuable Help Mr.royUK

**royUK** · Oct 1st 2020

Pleased to help

VBA required to replace text only at last part of sentence

Participate now!

Share