Looking to find a way of highlighting when 10055306 if present in a range.

[JoRo_]

This is not my spreadsheet and pulls data in from many sources, so the cell listing the ranges cannot be expanded using text to columns.

10055306

10055274-10055303,10055305-10055307

 

[felixstraube]

Lets assume you have your data in columna A starting from A2, how about using this formula in your conditional formatting:

=ISNUMBER(SEARCH("10055306", $A2))

 

[Joe4]

Lets assume you have your data in columna A starting from A2, how about using this formula in your conditional formatting:

=ISNUMBER(SEARCH("10055306", $A2))

That won't work - look a little more closely at the question.

Some of the values in column A are number spans, and they want to know if the number is between any of those spans, i.e. look at their example:
If the value in A2 is:

10055274-10055303,10055305-10055307

and they want to see if:

10055306

exists in any of those spans, that number does NOT explicitly appear in cell A2, but falls between the last span:

10055305-10055307

So in that case, your formula would return FALSE, when it should return TRUE.

 

[felixstraube]

.

 

[Joe4]

Ohh, i see the coma should be seen as a column separator.
Try this formula then:

Book11.xlsx
	A	B	C
1
2	10055274-10055303	10055305-10055307	0
3	10055274-10055303	10055306-10055307	1
Sheet4

Cell Formulas
Range	Formula
C2:C3	C2	=SUM(ISNUMBER(SEARCH("10055306", $A2:$B2&""))*1)

According to their requirements, it doesn't sound like they can expand it to multiple columns like that:

Quote from original post:
"This is not my spreadsheet and pulls data in from many sources, so the cell listing the ranges cannot be expanded using text to columns."

 

[felixstraube]

You are right,

Maybe this will work:?

Book11.xlsx
	A	B	C
1
2	10055274-10055303,10055305-10055307		1
Sheet4

Cell Formulas
Range	Formula
C2	C2	=SUM(MAP(TEXTSPLIT($A2, ","), LAMBDA(x, LET(v, VALUE(TEXTSPLIT(x, "-")), v_1, INDEX(v, 1, 1), v_2, INDEX(v, 1, 2), mV, 10055306, (mV>=v_1)*(mV<=v_2) )) ))

 

[Joe4]

You are right,

Maybe this will work:?

Book11.xlsx
	A	B	C
1
2	10055274-10055303,10055305-10055307		1
Sheet4

Cell Formulas
Range	Formula
C2	C2	=SUM(MAP(TEXTSPLIT($A2, ","), LAMBDA(x, LET(v, VALUE(TEXTSPLIT(x, "-")), v_1, INDEX(v, 1, 1), v_2, INDEX(v, 1, 2), mV, 10055306, (mV>=v_1)*(mV<=v_2) )) ))

I have Excel 365, but that returns a #VALUE error for me.

Even if it does work, it is making a bunch of assumptions, i.e.
1. The user has Excel 365 so they have access to the LAMBDA function.
2. It appears that your formula may only look for 2 spans. However, it is not clear from the question if that is always true.
3. The user was not really clear on the scope of this question:
- are they looking for a single value within a single cell?
- are they looking for a single value within a range of cells?
- are they looking for multiple values across a range of cells?

So, some clarification from the user about which version of Excel they are using and the full scope of the question would be helpful!

 

[felixstraube]

Yes of course all those question would have to be answered.

I have Excel 365, but that returns a #VALUE error for me.

That's weird. It works for me: