OpenOffice Calc regex returns wrong value - what's wrong with my regex?

Question

I have a column of text with values concatenated by my bank, as in a typical monthly statement.

Here is an example (column A240):

OCT 26 BALANCEPROTECTOR PREMIUM $10.05

I'm trying to set the value of a separate column to be the numeric value of the dollar amount. Here is the current formula currently, set up in column G240:

=SEARCH("\$[0-9]{1,6}\.[0-9]{2}";CLEAN(A240))

I am expecting to see 10.05 in the result, but what shows up there is $33.00 (column is set to currency). The raw value going into it is 33, shown if I hit default formatting.

Is there something wrong with my regex, or am I just missing some options? I have tried showing =CLEAN(A240) and everything looks like it does in A240 so I know the formula's receiving all the necessary data.

Any thoughts or improvements?

Thanks!

UPDATE:

I realized that the search function returns the position of the regex, not the string. After reading this, I updated the formula and now it works as expected:

=VALUE(MID(CLEAN(A240);SEARCH("\$[0-9]{1,6}\.[0-9]{2}";CLEAN(A240))+1;LEN("\$[0-9]{1,6}\.[0-9]{2}")))

Answer 1

With a bit of help from pnuts pointing me in the right direction, I figured out the OpenOffice Calc way of doing it without REGEX:

=RIGHT(A240;LEN(A240)-SEARCH("$";A240))

Note the use of semicolons and the missing bracket.

Using REGEX solved my issue in March though, I solved it this way:

=VALUE(MID(CLEAN(A240);SEARCH("\$[0-9]{1,6}\.[0-9]{2}";CLEAN(A240))+1;LEN("\$[0-9]{1,6}\.[0-9]{2}")))

Answer 2

Use single quotes. This worked with egrep, but double quotes didn't work with egrep.