Is there a way to find any one of a set of characters using an excel formula

Question

I have data that uses a range, or a less than symbol to denote 'between 0 and number'. But multiple characters are used for the same purpose.

It looks like below (first two columns), plus a column showing the results I want:

Country	Average hotdog consumption	Desired output
Madeupaland	10-200	105
Exampledesh	50—1000	525
Republic of Notreal	<1000	500
Inventia	≤5000	2500

Plus many rows where the data in the second column is purely numerical and doesn't need finessing into a number

I can use this formula to calculate the midpoint where there is a range:

=IFERROR(AVERAGE(LEFT(C2,FIND("–",C2)-1),RIGHT(C2, LEN(C2)-FIND("–",C2))), A2)

But they only covers one kind of dash(- and not —). Similarly, if I want to halve the numbers in rows with < and ≤ I'd need to replicate a formula there.

Is there a way of finding multiple different characters from a set? My understanding is that find looks for the whole string of characters. substitute is a work around, but I'd have to substitute every different value in the 'character set'.

In regex this would just be [-—].

I'm using Excel 2013 if that matters

Answer 1

It's not a perfect solution but you can try the following. This replaces those patterns of text with replacements representing which formula to use:

1. Create a Reference Table (I have made this in I1:K5)
   |Pattern |Pattern Name |Substitution Rule | |------- |------------ |----------------- | |— |double dash |/2+0.5* | |- |dash |/2+0.5* | |< |lt |0.5* | |? |lte |0.5* |

2. In your third column enter the following array formula (Using Ctrl + Shift + Enter to confirm) =IF(ISNUMBER(B2),B2,"'="&SUBSTITUTE(B2,INDEX($I$2:$I$5,MIN(IF(ISNUMBER(FIND($I$2:$I$5,B2)),ROW($I$2:$I$5)-1,99))),INDEX($K$2:$K$5,MIN(IF(ISNUMBER(FIND($I$2:$I$5,B2)),ROW($I$2:$I$5),99)-1))))

3. Copy your third column and past values into a fourth column

4. Replace all the ''s with nothing to evaluate the expressions using Ctrl + H

My Result:

Country	Average hotdog consumption	Desired output	Formula Paste	Output after replacing 's
Madeupaland	10-200	105	'=10/2+0.5*200	105
Exampledesh	50—1000	525	'=50/2+0.5*1000	525
Republic of Notreal	<1000	500	'=0.5*1000	500
Inventia	?5000	2500	'=0.5*5000	2500