Use wildcard in VBA

krokodilizm

New Member
Joined
Feb 3, 2016
Messages
20
I have a column of invoice numbers that are not consistent. Some are 4 digits, some are 6 digits (they include the year in front, e.g. 16xxxx). The Find and Replace function is limited. How can I employ VBA to locate all cells that start with 16 and remove the two prefixes?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Column H. Given the VBA code it is easy to tweak the references, no? So I thought it doesn't matter which row/column you reference in the template.
 
Upvote 0
Your assumption that you do not need to provide specifics because you can just modify the code to fit your needs may be true with some people if they know Vba. But then why is it difficult to provide the specifics. And if the user knows Vba well enough to modify the code I would wonder why the user cannot write their code from scratch.
Try this:
Code:
Sub Remove_Sixteen()
Dim c As Range
Dim Lastrow As Long
Application.ScreenUpdating = False

Lastrow = Cells(Rows.Count, "H").End(xlUp).Row
    
    For Each c In Range("H1:H" & Lastrow)
        If Left(c.Value, 2) = "16" Then
            c.Value = Right(c.Value, Len(c.Value) - 2)

        End If
    Next
    
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Wow thanks. Really appreciate your time. The code runs smoothly.
I am new to VBA but I can somewhat deduce what is going on when I see a code. It's the commands and the syntax that I can't derive from scratch.
 
Upvote 0
Glad I was able to help you and that your reading the code and trying to learn Vba and not just searching for answers. Come back here to Mr. Excel next time you need some assistance.
Wow thanks. Really appreciate your time. The code runs smoothly.
I am new to VBA but I can somewhat deduce what is going on when I see a code. It's the commands and the syntax that I can't derive from scratch.
 
Upvote 0
Your original question is somewhat open to interpretation to me.

- Do all the 6-character cells start with "16" or could they also start with, say, "15" and if so should that be removed too?

- Is it possible that any of the 4-character cells start with "16" and if so should it still be removed?

In any case, there would be a way to do the required replacements all at once, rather than looping a row at a time.
For example, this would just keep the last 4 characters from each cell.
You are right about easily being able to switch columns, so I'll leave you to that if you want to try this code.

Code:
Sub Keep4()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#),right(#,4),"""")", "#", .Address))
  End With
End Sub
 
Last edited:
Upvote 0
If the requirement is to remove only leading "16"s from any cell, as MAIT's code does, then this should also do that in one hit:
Code:
Sub Remove_16()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(left(#,2)=""16"",mid(#,3,len(#)),if(len(#),#,""""))", "#", .Address))
  End With
End Sub
 
Upvote 0
Your original question is somewhat open to interpretation to me.

- Do all the 6-character cells start with "16" or could they also start with, say, "15" and if so should that be removed too?

- Is it possible that any of the 4-character cells start with "16" and if so should it still be removed?

In any case, there would be a way to do the required replacements all at once, rather than looping a row at a time.
For example, this would just keep the last 4 characters from each cell.
You are right about easily being able to switch columns, so I'll leave you to that if you want to try this code.

Code:
Sub Keep4()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#),right(#,4),"""")", "#", .Address))
  End With
End Sub

Well spotted. That was the original idea, to keep the 4 characters on the right hand side.
I am adding all these codes to a library of text files with detailed file names for casual use. I will try to derive other combinations based on this (keep middle 4, left 4 and other combos as an exercise). Give a man a fish..., right? :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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