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
 

iyyi

Active Member
Joined
Jun 5, 2012
Messages
353
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
 

killpaddy

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

Active Member
Joined
Jun 5, 2012
Messages
353
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:

killpaddy

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

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top