Hi i would like to remove everything that is on the left side of the cell value with 2 conditons
Condition 1: Start removing the left side of the cell when "P" is found and followed by a number Or
Condition 2: Start removing the left side of the cell when "PO" is found and followed by a number
I have tried this code but it doesnt seem to meet my conditions when more there are more than 1 "P" inside the cell and it also doesnt work when "PO" is inside the cell as shown in Column B row 3 and row 2 respectively. Please advice on how i can modify my code to make it work.
Condition 1: Start removing the left side of the cell when "P" is found and followed by a number Or
Condition 2: Start removing the left side of the cell when "PO" is found and followed by a number
I have tried this code but it doesnt seem to meet my conditions when more there are more than 1 "P" inside the cell and it also doesnt work when "PO" is inside the cell as shown in Column B row 3 and row 2 respectively. Please advice on how i can modify my code to make it work.
VBA Code:
Sub deleteleft()
Dim ws As Worksheet
Dim LR As Long
Dim cell As Range
Dim startPosition As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
LR = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For Each cell In ws.Range("B2:B" & LR)
If VarType(cell.Value) = vbString Then
startPosition = InStr(1, cell.Value, "P", vbTextCompare)
If startPosition > 0 Then
If IsNumeric(Mid(cell.Value, startPosition + 1, 1)) Or (Mid(cell.Value, startPosition + 1, 2) = "O" And IsNumeric(Mid(cell.Value, startPosition + 2, 1))) Then
cell.Value = Mid(cell.Value, startPosition)
End If
End If
End If
Next cell
End Sub
VBA Code:
Last edited by a moderator: