Using wildcards to filter list - help

[jaydee]

Hi guys,

I have a list of data and I am trying to filter for certain text. I tried using the wildcards ?, ~, and *, but I can't get it to work properly.

I want to filter for anything with a character, followed by a dash, and 5 numbers (i.e. M-16263, B-16252)


For example, out of the list below, I am trying to filter for "BD BAKER KAUAI LLC/A-16082", "BEAVERS/H-16289", "BLAISDELL/K-16276", "BLANCO-MEDEIROS/B-16288".
Can someone help please? Thank you.











BD BAKER KAUAI LLC/A-16082



BEADEL



BEADEL



BEADEL



BEAVERS/H-16289



BEK - KAANAPALI OCEAN RESORT



BELFOR HAWAII PROPERTY RESTORATION



BENEDICT (MDL 600)



BENJAMIN VERGARA / 25TH



BERARDY RESIDENCE



BETH MCCORMICK



BILL & CHRISTINA BART



BILL KENNEDY



BILL PIAZZE MODEL 1200 HOKULANI



BISHOP



BJ OTT'S



BLAISDELL/K-16276



BLANCO-MEDEIROS/B-16288



BLANKLEY - TOM BRYAN CONSTRUCTION



M/M KEN AKINAKA (MDL 500 KAU'I MOD)
M/M MEL PARK
M/M TOKU ISHIKAWA (MOD 700)
MACIEL HOMES / 4TH AVE. JOB#123


M/M KEN AKINAKA (MDL 500 KAU'I MOD)


M/M MEL PARK


M/M TOKU ISHIKAWA (MOD 700)


MACIEL HOMES / 4TH AVE. JOB#123

[jolivanes]

Maybe?

Sub Maybe()
Application.ScreenUpdating = False
With Sheets("Sheet1").UsedRange
.AutoFilter 1, "*?" & "-" & "?????"
'Do what you need to do here
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

[p45cal]

The wildcards used in built-in worksheet formulae don't differentiate between digits and alpha characters (someone please tell me different!) so although a formula on the sheet using only built-in functions is possible, it would be complex and difficult to manage/maintain.
With the help of a simple user-defined function in vba you can get round it:
Function blah(rng) As Boolean
If rng.Value Like "*[A-Z]-#####*" Or rng.Value Like "*[a-z]-#####*" Then blah = True
End Function

In the attached are 2 sheets with your data (some test data added highlighted in red).
In Sheet1 I've used the udf in a helper column, now you just have to filter that column for TRUE.
In Sheet2 the same data, but I've used Advanced Filter instead to create a new shortlist, where the criteria range is highlighted in green and should include the blank cell therein.

If you're dead certain the single character before the hyphen will always be a capital letter in your data then you can shorten the udf to:
Function blah(rng) As Boolean
If rng.Value Like "*[A-Z]-#####*" Then blah = True
End Function

[jaydee]

Thanks p45cal and jolivances!

The UDF worked great. I was able to delete those specific rows from my data set and it saved me a LOT of time from looking at thousands of rows manually.

Thank you again for your time and consideration. I really appreciate your help.

Warm Regards,
Justin

[snb]

Using formulae:

[p45cal]

Using formulae:!!??
Row 22 is different, and the rest are the same only by luck.
The formula only tries to check for a single number after the hyphen, but even it it doesn't find one (rows 19,21,22,23 where there's a letter after the (first) hyphen) it still returns 0, so the requirement to find 5 digits isn't there.
BLANCO-MEDEIROS/ would incorrectly return 0, while BLANCO-MEB-16288DEIROS/ correctly returns 0.
I still stand by my first sentence in msg#3 but would love to be proved wrong.

[snb]

Based on the real examples the TS provided:

[p45cal]

In which case it can be shortened to =IFERROR(SEARCH("/",A2)* SEARCH("-",A2),"")
Over to jaydee…