Discussion Re: Formula to find specific random text in a cell in Excel
https://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2138853#M89668
<P><LI-USER uid="968010"></LI-USER> </P>
<P>SEARCH accepts * and ? as wildcards, but not #.</P>
<P>If you use Excel on Windows, you can use a custom VBA function:</P>
<LI-CODE lang="visual">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</LI-CODE>
<P>Let's say you want to apply conditional formatting to cells in column B, starting in B2.</P>
<P>Use the following formula in your conditional formatting rule:</P>
<LI-CODE lang="excel">=Check(B2)</LI-CODE>
<P> Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.</P>Tue, 16 Feb 2021 11:04:27 GMTHans Vogelaar2021-02-16T11:04:27ZFormula to find specific random text in a cell
https://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2138791#M89667
<P>Hi,</P><P> </P><P>I am somewhat of a novice in Excel but I'm working on a project for work.</P><P>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...</P><P>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</P><P> </P><P>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...</P><P>=ISNUMBER(SEARCH("??# to ??#",$L6)) as I figured ?? would look for 2 letters and # the one number.</P><P>I then format cells to highlight red.</P><P> </P><P>But this formula isn't working, any ideas how I could do this?</P><P> </P><P>Thank you</P>Tue, 16 Feb 2021 10:43:56 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2138791#M89667Tez_Clews2021-02-16T10:43:56ZRe: Formula to find specific random text in a cell
https://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2138853#M89668
<P><LI-USER uid="968010"></LI-USER> </P>
<P>SEARCH accepts * and ? as wildcards, but not #.</P>
<P>If you use Excel on Windows, you can use a custom VBA function:</P>
<LI-CODE lang="visual">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</LI-CODE>
<P>Let's say you want to apply conditional formatting to cells in column B, starting in B2.</P>
<P>Use the following formula in your conditional formatting rule:</P>
<LI-CODE lang="excel">=Check(B2)</LI-CODE>
<P> Don't forget to save the workbook as a macro-enabled workbook, and make sure that you allow macros when you open it.</P>Tue, 16 Feb 2021 11:04:27 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2138853#M89668Hans Vogelaar2021-02-16T11:04:27ZRe: Formula to find specific random text in a cell
https://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2140763#M89745
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Works perfectly thank you,</P><P> </P><P>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.</P><P> </P><P>Managed to adapt it for different criteria so thank you again</P>Tue, 16 Feb 2021 22:27:10 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-find-specific-random-text-in-a-cell/m-p/2140763#M89745Tez_Clews2021-02-16T22:27:10Z