Text to columns based on value

[andysh]

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?

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

 

[Fluff]

How about

For i = 1 To LR
   If Cells(i, 3) Like "S########" Then Cells(i, 3) = Mid(Cells(i, 3), 2)
Next i

 

[davesexcel]

See if the 1st char is "S" then see if the string is len(9), then see if the last 8 chars are numeric

Sub Button1_Click()
Dim sh As Worksheet
Dim rng As Range
Dim c As Range

Set sh = ActiveSheet
With sh
Set rng = .Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
For Each c In rng.Cells

If Left(c, 1) = "S" And Len(c) = 9 And IsNumeric(Right(c, 8)) Then
c = Right(c, 8)
End If
Next
End With


End Sub

 

[Fluff]

@davesexcel
Without knowing what the OP's data is like, that will give the wrong result if a cell has a value of S50090E88

 

[davesexcel]

Ah, yes digit are not just numbers, guess I could have left out the 3rd condition.

 

[Fluff]

I consider digits to be numbers, but isnumeric will return true for 50090E88 even though it's not a number.

 

[andysh]

Perfect, thanks Fluff, your initial idea works a treat

And it's a lot less code than text to columns, stored in the sponge for future use with other files

 

[Fluff]

Glad we could help & thanks for the feedback.