Extract numerical values in multiple different text strings

[Robarb]

Hello all,

Going a bit mad with this one and I think the solution is simple but I seem to be having a brain melt.

I have a 2 columns with headers (Column A and B) of text strings (see image below).

In a third column (C) I would like to extract the Lowest number from the text string; this will generally be either the first number up to the "-" character, or the first number between the character "<" and the first "m" of mm.

In a fourth column (D) I would like to extract the Highest number from the text string; this will generally be either the only number between the character "<" and the first "m" of mm or the second number between "-" character and the first "m" of mm.

I've tried all the usual suspects with RIGHT, LEFT, MID, MIN, LEN, SEARCH, FIND, OR, SUMPRODUCT but can't seem to get the right combo or formula working properly. Any ideas?

I would really appreciate some clarity on this one. Thank you

 

[kweaver]

How does the B column come into play?

Using the A column, C2 could be:

=IF(ISERROR(FIND("-",A2)),MID(A2,FIND("<",A2)+1,FIND("m",A2)),1*LEFT(A2,FIND("-",A2)-1))

filled down.
The D column could be:

=IF(ISERROR(FIND("-",A2)),1*MID(A2,FIND("<",A2)+1,FIND("m",A2)-FIND("<",A2)-1),1*MID(A2,FIND("-",A2)+1,FIND("m",A2)-FIND("-",A2)-1))

filled down.

Might not be the slickest way, but seems to do the trick.

(per your reply, change A to B in my column D code)

 

[Robarb]

Ah yes good point. So Column C should be the result of extraction from Column A, and Column D should be the result of the extraction from Column B.

Like this:

I have typed in the black numbers in this example, it would be good if i could get a formula to do it.

Thanks

 

[kweaver]

(per your reply, change A to B in my column D code)

 

[sandy666]

is that what you want?

Left	Right	Lower	Higher
100-200mm	200-300mm	100	300
100-200mm	300-400mm	100	400
<100mm	100-200mm	100	200
<100mm	200-300mm	100	300
100-200mm	500-600mm	100	600
<100mm	400-500mm	100	500
<100mm	100-200mm	100	200
<100mm	100-200mm	100	200
<100mm	200-300mm	100	300
200-300mm	800-900mm	200	900
<100mm	100-200mm	100	200
<100mm	300-400mm	100	400
<100mm	300-400mm	100	400
100-200mm	500-600mm	100	600
100-200mm	300-400mm	100	400
<100mm	100-200mm	100	200
<100mm	100-200mm	100	200
<100mm	<100mm	100	100
<100mm	200-300mm	100	300
<100mm	<100mm	100	100
100-200mm	400-500mm	100	500
<100mm	100-200mm	100	200
<100mm	200-300mm	100	300
<100mm	100-200mm	100	200
<100mm	<100mm	100	100
<100mm	300-400mm	100	400
700-800mm	1000-1100mm	700	1100

 

[Robarb]

@kweaver Thank you very much!!