SOLVED

Formula to find specific random text in a cell

Copper Contributor

Hi,

 

I am somewhat of a novice in Excel but I'm working on a project for work.

I am trying to use a formula to conditional format a row when text is found in a cell, however the text although will always be the same format will be any combination of letters/number...

i.e. AB1 to CD2, or AD2 to CB1, or ZG3 to YH7... will always have two letters and one number but could be any letters and numbers. It could also have leading/trailing text i.e. From AB1 to CD2, or AB1 to CD2 transfer

 

Selecting cells A6 to L6 as this is the range I am working with the data I am looking for in L6, I go in to conditional format and enter this formula...

=ISNUMBER(SEARCH("??# to ??#",$L6)) as I figured ?? would look for 2 letters and # the one number.

I then format cells to highlight red.

 

But this formula isn't working, any ideas how I could do this?

 

Thank you

2 Replies
best response confirmed by Tez_Clews (Copper Contributor)
Solution

@Tez_Clews 

SEARCH accepts * and ? as wildcards, but not #.

If you use Excel on Windows, you can use a custom VBA function:

Function Check(s As String) As Boolean
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[A-Za-z]{2}[0-9] to [A-Za-z]{2}[0-9]"
    Check = re.Test(s)
End Function

Let's say you want to apply conditional formatting to cells in column B, starting in B2.

Use the following formula in your conditional formatting rule:

=Check(B2)

 Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.

@Hans Vogelaar 

 

Works perfectly thank you,

 

I thought VBA was the way to go as used it in another project but still very much a novice, was not aware of conditional format to VBA linking.

 

Managed to adapt it for different criteria so thank you again

1 best response

Accepted Solutions
best response confirmed by Tez_Clews (Copper Contributor)
Solution

@Tez_Clews 

SEARCH accepts * and ? as wildcards, but not #.

If you use Excel on Windows, you can use a custom VBA function:

Function Check(s As String) As Boolean
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[A-Za-z]{2}[0-9] to [A-Za-z]{2}[0-9]"
    Check = re.Test(s)
End Function

Let's say you want to apply conditional formatting to cells in column B, starting in B2.

Use the following formula in your conditional formatting rule:

=Check(B2)

 Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.

View solution in original post