Hi I have successfully used ( =RIGHT(B3;LEN(B3)-SEARCH(" £";B3;1)) ) to extract my value from a string but how do I total the extracted values
Total Price £0.45 £0.45
Total Price £1.10 £1.10
Total Price £0.45 £0.45
Total Price £1.00 £1.00
Total Price £0.58 £0.58
Grand Total £0.00
PS How do you thank people for their help
[Edit - Opaque] Formatted table as preformatted text for better readability.
=RIGHT(B3;LEN(B3)-SEARCH(" £";B3;1))
You can’t add text and function RIGHT() (as well as REGEX() does) evaluates to text data type. Therefore you need to extract digits and dot only, turn that text into a value (decimal number) and use a proper currency formatting - please see my modified answer below.
If an answer works for you click the check mark (
) next to the answer (additionally you may want to write a comment below the answer but don’t add your own answer. Answers are reserved for solution to a problem). That’s how it works here.
Hello,
modify my answer in previous post to:
=VALUE(REGEX(TRIM(A2);"[:digit:]+\.[:digit:]{2}$"))
(Extracts without £-sign and turns the string to a number)
Format the resulting cells using Right click > Format Cells... and use
Format Code [$£-809]#,##0;-[$£-809]#,##0 (format as UK currency)
Use formula =SUM(B2:B5) (see cell B7 in sample file)
See following sample sheet: ExtractPriceFromStringAsValues.ods
PS You may also change @schiavinatto’s formula to:
=VALUE(RIGHT(A2;LEN(A2)-SEARCH(" £";A2;1)-1))
Hope that helps.