Hi,
I think I'm pretty close with this. I'm trying to remove a leading letter S from product codes but not all cells in the column are product codes, so what i'd like the vba to do is:-
Look at cells in col C
If cell value is 'S' followed by 8 digits
remove 'S' and leave 8 digits
else
leave cell contents as they are
I have the below but it isn't working, any suggestions?
I think I'm pretty close with this. I'm trying to remove a leading letter S from product codes but not all cells in the column are product codes, so what i'd like the vba to do is:-
Look at cells in col C
If cell value is 'S' followed by 8 digits
remove 'S' and leave 8 digits
else
leave cell contents as they are
I have the below but it isn't working, any suggestions?
VBA Code:
Dim LR As Long
Dim i As Integer
LR = Cells(Rows.Count, 3).End(xlUp).Row
For i = 1 To LR
If InStr(Cells(i, 3), "S########") <> 0 Then
Cells(i, 3).TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="S", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
End If
Next i