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 ?
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 ?
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!