Finding a value within a number/text

**Flower R** · 12-31-2008, 10:06 AM

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** · 12-31-2008, 10:10 AM

Hi,

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

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

**Flower R** · 12-31-2008, 10:21 AM

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

**Mark@Work** · 12-31-2008, 10:25 AM

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** · 12-31-2008, 10:30 AM

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.

**oldchippy** · 12-31-2008, 10:41 AM

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** · 12-31-2008, 11:24 AM

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

Please Login or Register  to view this content.

in c1

Please Login or Register  to view this content.

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** · 12-31-2008, 02:50 PM

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** · 12-31-2008, 03:12 PM

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