PDA

View Full Version : Remove page info in cell



swaggerbox
08-11-2016, 03:26 AM
In each cell in column A, I need to remove a pagination reference that is patterned this way, [30-characterAddress]İMyCompanybbbYYY[43-character pagination details]
Both [30-characterAddress] and[43-character pagination details] could vary in each cell. Is there a way to do that using a function? See example below:


Example string: This is an example where I need to remove the string that starts 30 characters to the left of İMyCompanybbbYYY up to 43 characters to the right of string today.
Preferred Output: This is an example where I need to remove the string that starts today.

Paul_Hossler
08-11-2016, 02:06 PM
Maybe



Option Explicit
Sub test()
Dim r As Range
Dim i As Long, n As Long

n = Len("MyCompanybbbYYY")

For Each r In ActiveSheet.Cells(1, 1).CurrentRegion.Columns(1).Cells
i = InStr(r.Value, Chr(169))
r.Value = Left(r.Value, i - 31) & Right(r.Value, Len(r.Value) - 43 - i - n)
Next

End Sub

swaggerbox
08-11-2016, 11:23 PM
Thanks Paul. How to address multiple instance of the same pattern in a single cell?

Paul_Hossler
08-12-2016, 06:38 AM
not tested but probably something like this


Option Explicit

Sub test2()
Dim r As Range
Dim i As Long, n As Long

n = Len("MyCompanybbbYYY")

For Each r In ActiveSheet.Cells(1, 1).CurrentRegion.Columns(1).Cells
Do While InStr(r.Value, Chr(169)) > 0
i = InStr(r.Value, Chr(169))
r.Value = Left(r.Value, i - 31) & Right(r.Value, Len(r.Value) - 43 - i - n)
Loop
Next
End Sub