# 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.

• Try,

In B1, formula copied down :

=0+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,FIND("vs",A1)-FIND("EPS",A1)-5),"\$",""),"c","%")

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

• Then,

Change B1 formula to :

=0+SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,99)," ",REPT(" ",50)),50),"\$",""),"c","%")

and copied down

• 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.