Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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}")))
share|improve this question
    
Hi and welcome to SU. Thanks for reporting back with your solution, please post it as an answer and accept it so the question can be marked as answered. –  terdon Mar 28 '13 at 0:37

2 Answers 2

up vote 1 down vote accepted

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}")))
share|improve this answer

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

share|improve this answer
    
Unfortunately using single quotes gives a #NAME? error, instead of a wrong result. –  user209448 Mar 22 '13 at 8:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.