Ask Your Question
0

Calc: How do I extract part of a cell [closed]

asked 2013-11-03 15:46:42 +0200

ktz84 gravatar image

updated 2015-11-12 20:35:26 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-12 20:35:44.144834

1 Answer

Sort by » oldest newest most voted
1

answered 2013-11-03 16:41:09 +0200

ktz84 gravatar image

updated 2013-11-03 16:50:13 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-11-03 15:46:42 +0200

Seen: 3,420 times

Last updated: Nov 03 '13