Extract numeric substring after specific text

[Bering]

Hello,

I would like to extract a numeric substring after a specific text: in the example below, I would need the formula to return the numeric part of the string after "equivalent to ", hence 2,500,000.

Many thanks in advance for any ideas, formulae or vba.

equivalent to 2,500,000 USD at eur/usd rate 1.726

 

[Sanjeev1976]

Hello,

I would like to extract a numeric substring after a specific text: in the example below, I would need the formula to return the numeric part of the string after "equivalent to ", hence 2,500,000.

Many thanks in advance for any ideas, formulae or vba.

If the text is in cell A1, you can use the below formula in B1
=VALUE(TRIM(MID(A1,14,FIND("USD",A1)-15)))

 

[eduzs]

Maybe something like:
(the value must be always between "to" and "USD")

=MID(A1,SEARCH("to ",A1)+3,SEARCH(" USD",A1)-SEARCH("to ",A1)-3)

 

[Bering]

thanks everyone, however I should have made clear that the currency could be different than the one I used in my example...

 

[Fluff]

How about

=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("to ",A2)+2,"")," ",REPT(" ",100)),100))

 

[Bering]

How about

=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("to ",A2)+2,"")," ",REPT(" ",100)),100))

Many thanks Fluff, that's perfect Below is how I changed the formula to keep the substring numeric:

IFERROR(VALUE(TRIM(LEFT(SUBSTITUTE(REPLACE(E3,1,FIND("to ",E3)+2,"")," ",REPT(" ",100)),100))),0)

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[eduzs]

How about

=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("to ",A2)+2,"")," ",REPT(" ",100)),100))

Superb! Saved in my notes. ?