Text to columns based on value

andysh

Board Regular
Joined
Nov 8, 2019
Messages
105
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?


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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,425
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For i = 1 To LR
   If Cells(i, 3) Like "S########" Then Cells(i, 3) = Mid(Cells(i, 3), 2)
Next i
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
See if the 1st char is "S" then see if the string is len(9), then see if the last 8 chars are numeric

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,425
Office Version
  1. 365
Platform
  1. Windows
@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

Well-known Member
Joined
Feb 26, 2006
Messages
1,026

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,425
Office Version
  1. 365
Platform
  1. Windows
I consider digits to be numbers, but isnumeric will return true for 50090E88 even though it's not a number.
 

andysh

Board Regular
Joined
Nov 8, 2019
Messages
105
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,425
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,494
Messages
5,548,375
Members
410,830
Latest member
Tubtechbob
Top