Pattern Matching in Find/ Replace VBA Excel

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
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
Code:
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
For the first question assuming the data represent all your data, you can use this macro
Code:
 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
 
Upvote 0
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.
 
Upvote 0
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
Code:
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!
 
Last edited:
Upvote 0
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)'
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top