Calc: How do I extract part of a cell

[ktz84]

I need to extract the exchange rate from bank statement. The cell contains a lot of information however the exchange rate is immediately preceeded by VRATE and a space and the exchange rate itself is always has the following format 0.0000. I’m using version: 4.1.2.3.

Eg

Random text VRATE 1.1245, more random text and my extracted value in a new cell would look like this:

1.1245

Thanks

[ktz84]

OK finally worked out how to do this with the following formula

=VALUE(MID(B2,FIND("VRATE",B2)+6, 6))

So basically I used FIND to find VRATE in the cell and the used MID to move 6 characters to the right and start extracting there and finishing 5 characters after that (6 in total if you include the starting character hence the 6). I also used VALUE as the extracted number was formatted as text so VALUE ensures that it recognised as a number.

You can use SEARCH instead of FIND as that is case insensitive. If the length of extracted text is variable then you will need to get more creative.