Finding complex partial string

[cortexnotion]

Hi All

I am stuck with how to search a complex string that has so many wildcards. The string format is always "B?W/?????/??/??" or "H?W/?????/??/??" where the question marks are always a number, the "B" or "H" are the constant values in 1st character of string, "W" is always 3rd character, and the string can appear anywhere in the cell and may not always have a space before and/or after it.

As an example I was thinking...

If Sheets(1).Range("A1") = "*B?W/?????/??/??*" then Msgbox "Match"

but that doesn't seem to work.

Any help appreciated on how to detect the string!

Project 1 B6W/12765/01/02 Road repair

H6W/12483/01/17 Project 2 unknown status

approved Project 3 continue status

Project 4 H6W/12483/01/18locked radio soon

B6W/12765/01/04Project 5 further funding

Project 6 aborted report back

 

[Peter_SSs]

You could employ a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetStr(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(B|H)\dW/\d{5}/\d{2}/\d{2}"
    If .Test(s) Then GetStr = .Execute(s)(0)
  End With
End Function

cortexnotion.xlsm
	A	B
1
2	Project 1 B6W/12765/01/02 Road repair	B6W/12765/01/02
3	H6W/12483/01/17 Project 2 unknown status	H6W/12483/01/17
4	approved Project 3 continue status
5	Project 4 H6W/12483/01/18locked radio soon	H6W/12483/01/18
6	B6W/12765/01/04Project 5 further funding	B6W/12765/01/04
7	Project 6 aborted report back
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=GetStr(A2)