Finding a value within a number/text

[Flower R]

Hello,

Is there any formula to search a value which lies within a number, for example if in A1, a value has been stored as: 051-01-12 or 051!01:12, and it is required to search a value like 05101 to 05112, the search function may hit the value stored as 051-01-12 or 051!01:12. Thanks.

Flower R.

[oldchippy]

Hi,

If these text values are in column A, can you use this in column B then search on these?

=SUBSTITUTE(A1,"-","")

[Mark@Work]

Old Chippys formula can of course be expanded as:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","") ,"!",""),":","")
to catch all the three possible deliminators you mentioned.

Alternatively the EXCEL search allows "wild-cards" so you could search for 051?01?12 which would match with any deliminator.

Mark.

[Mark@Work]

Your second post came through while I was writing mine, and made mine irrelevent!
I've got to go now, but I expect old chippy can help you
(he's usually pretty expert).

Mark.

[Flower R]

Thanks for reply but that does not serve the purpose. the formula as changed the value from 001-01-12 as a solid value 0010112 and if I try to find 00109 which lies within range it will not give anything. Actually 001-01-12 is to be taken as 00101 to 00112 so any number searched within 00101 to 00112 may be coming through.
Flower R

[oldchippy]

Well thanks for the compliment Mark@Work, you're not doing to badly yourself.

Flower R can you post a sample of your sheet together with the expected result?

[martindwilson]

to extract begining and end of range from your two posted examples
in b1

=--LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","",1),"!",""),":","-"),(FIND("-",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","",1),"!",""),":","-")))-1)

in c1

=--(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"!","-"),":","-"),FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"!","-"),":","-"))-1)&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","",1),"!",""),":","-"),(FIND("-",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","",1),"!",""),":","-")))+1,255))

then in d1 dragged down
=IF(AND($E$1>=B1,$E$1<=C1),"found","not found")
with value to find in e1 (may be a problem if the last bit after ! or - is greater than 00-99)

[Mark@Work]

Note that if the text is always three characters, then a delimiter, then 2 characters a delimiter then two characters, you could use the LEFT, MID and RIGHT instructions to rearrange the value.

thus B1 becomes : =LEFT(A1,3)&MID(A1,5,2)
and C1 becomes : =LEFT(B1,3)&RIGHT(B1,2)

Mark.

[Mark@Work]

Attached is a spread-sheet that attempts to provide a general solution that should be adaptable to a wide range of possible formats for your data.

Mark