Hello,
I need to look through cells in a column and mark them as true if they have the combination of "FR " or "FR" followed
by 5 numbers. E.g. FR12345 or FR 12345. The "FR" related combination can be anywhere in a text string in the cell. Can someone help share a formula that would be able to mark "True" if this combination is found?
Thanks.
Need to find specific text and number combination
May 21st, 2015 7:20am
For strings starting in A2, use this to return TRUE/FALSE
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),FALSE)
and copy down. If you only want the True values, use
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),IF(AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),"True",""),"")
- Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 4 hours 6 minutes ago
Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2015 9:07am
For strings starting in A2, use this to return TRUE/FALSE
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),FALSE)
and copy down. If you only want the True values, use
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),IF(AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),"True",""),"")
- Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Saturday, May 23, 2015 3:23 AM
May 22nd, 2015 1:07pm
For strings starting in A2, use this to return TRUE/FALSE
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),FALSE)
and copy down. If you only want the True values, use
=IF(ISNUMBER(SEARCH("FR",SUBSTITUTE(A2," ",""))),IF(AND(LEN(SUBSTITUTE(A2," ",""))>=SEARCH("FR",SUBSTITUTE(A2," ",""))+6,NOT(ISERROR(VALUE(MID(SUBSTITUTE(A2," ",""),SEARCH("FR",SUBSTITUTE(A2," ",""))+2,5))))),"True",""),"")
- Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Saturday, May 23, 2015 3:23 AM
Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2015 1:07pm
Try FX12345
May 22nd, 2015 11:08pm
I'm not sure what I was thinking.... ;-)
Thanks.
Bernie
Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2015 11:24pm
This topic is archived. No further replies will be accepted.
Other recent topics
