Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Hi,

Need help to extract numbers after specific text with two conditions. eg:-

1. 'EnWave reports Q3 EPS ($0.02) vs. (C$0.01) last year': in this case I need to extract the number coming after the text 'EPS' with brackets or shows as negative value, ie, as either (0.02) or -0.02.

2. 'EnWave reports Q3 EPS 56c vs. 80c last year': In the case when a letter 'C' comes after number, I need to divide it by 100 and shows as 0.56.

I prefer to do it as excel formula rather than VBA, since I am not good at it.

Thanks in advance!

Hi bosco_yip,

thanks for the quick reply. I sometimes get news headlines like below also, where the treatment is same.

eg: 'Ubiquiti Q4 Adj. EPS $0.47 Beats $0.34 Estimate, Sales $477.90M Beat $463.93M Estimate.' Here also I just want the number 2.47 if its positive, -2.47 if its negative or 0.47 if its like '47c'.

Can you help with this scenario ?

wow! many thanks for this formula. its works most of the time wonderfully saving me a lot of time, except in the below 2 scenarios.

1. when comma comes right after the number.

example 1: Canadian Western Bank Reports Q3 Adjusted EPS $1.01, Beating Forecast; example 2: Hibbett Sports reports Q2 EPS $2.86. consensus $1.42

2. when some text comes in between 'EPS' and number. example: EnWave reports Q3 EPS (C$0.00) vs. (C$0.01) last year.

can you please help ?

no other