PDA

View Full Version : [SOLVED] Find a Value in a Cell and Delete some of the text



Sarfaraz
01-04-2017, 09:53 PM
Hi,
I have a big spread sheet with multiple values, I like to write a code which find the value starting with PK and four numbers and remove these. like

Actual Value in the cell Desired Value in same cell after running the macro
PK0100 KARACHI KARACHI
PK0120 LAHORE LAHORE
PK2120 ISLAMABAD ISLAMABAD

I would be grateful if somebody help me writing this code

Paul_Hossler
01-04-2017, 10:09 PM
Select the data and try this




Option Explicit
Sub DeletePK0000()
Dim r As Range, c As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set r = Intersect(Selection, Selection.Parent.UsedRange)

On Error Resume Next
Set r = r.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If r Is Nothing Then Exit Sub

For Each c In r.Cells
If c.Value Like "PK#### *" Then c.Value = Right(c.Value, Len(c.Value) - 7)
Next
End Sub

Sarfaraz
01-05-2017, 09:22 PM
Thanks Paul, it worked perfectly fine. Thanks again