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 ?