Pattern Matching in Find/ Replace VBA Excel

[killpaddy]

Hi,

First of all I am trying to replace a part of a string in a list of data
e.g.
EXP4200-1
EXP4200-2
EXP4200-5
EXP4200-25
EXP4000-2
EXP4000-3
EXP3080-12
EXP3080-105
EXP3080-106
...

I want to delete (or replace with "") the 'EXP####-' part of the string, however I dont know how to use the 'pattern matching' style characters such as
'[A-K]', '*', '#' in VBA.

my guess is

Sheets(2).Columns("A").Replace What:= "EXP" & #### & "-", Replacement:= ""

(If '#' means 'any number', but im not sure)

I also have another similar problem where i want to replace "#, " with "#/" so only 'comma space' s that come after numbers (NOT letters) are replaced by a forward slash.
e.g.
'Quick Turn 15, 18, 20, 200
8 Station, 8 Position
SQT 200, 250
12 Station, 12 Position'

changes to:

'Quick Turn 15/18/20/200
8 Station, 8 Position
SQT 200/250
12 Station, 12 Position'

Thanks in advance for any help

 

[iyyi]

For the first question assuming the data represent all your data, you can use this macro

 Sub CleanA()
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Lr
    Cells(i, 1) = Right(Cells(i, 1), Len(Cells(i, 1)) - 8)
Next i
End Sub

 

[killpaddy]

Ive just found that some of my data in the first question is sometimes one character shorter (e.g. EXP800-1), so that code would not work for this case I dont think. I've come up with an alternative method for what I needed in the first question anyway, I just changed the numbers to '001' or '021' instead of '1' or '21', Thanks anyway.

Just need an answer to the second question, I think all I need is what the 'pattern matching' symbols are and how to use them in VBA if its possible.

 

[iyyi]

Just need an answer to the second question, I think all I need is what the 'pattern matching' symbols are and how to use them in VBA if its possible.

Try this For column A

Sub Slash()
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Lr
   If Len(Cells(i, 1)) - InStrRev(Cells(i, 1), ",") < 5 Then _
    Cells(i, 1) = Replace(Cells(i, 1), ", ", "/")
Next i
End Sub

Backup your data first!

 

[killpaddy]

In the second question, all of the information shown:
'Quick Turn 15, 18, 20, 200
8 Station, 8 Position
SQT 200, 250
12 Station, 12 Position'

is in one cell. Its not 4 separate cells. Other examples of cells are:

'Quick Turn 35, 40
12 Station, 24 Position'
OR
'Multiplex 6200, 6200Y, 4200
12 Station, 12 Position Multiplex 6250, 625, 425
12 Station, 12 Position
Mutliplex 420,620 (EXTERNAL ONLY)
12 Station, 12 Position
Hyper Quadrex 200 MSY
(Turn holder A & B only, Bore A only)'