# Strip $1.07B /$15.06M to 1070 / 15.06

Dear experts,

how do I strip dollar values formatted as text with B(illion) or M(illion) at the end? Formatting the cells as currency doesn't work, they are still not sortable / calculatable.

edit retag close merge delete

You surely meant 1070E6 and 15.06E6 respectively. A million is not a "default unit", is it?
(Are you aware of the fact that the usage of "Billion" in different languages/countries is extremely messed up. We won't get unambiguous large numbers in global commuinication again without abandoning the billions, trillions ... completely.)

( 2017-05-06 22:23:06 +0200 )edit

Is the field numeric, or text, i.e. sort of as Lupp is suggesting it must be numeric, but it also might be text, i.e. "$" + "1.07" + "B", so this conversion would need a different approach if that were the case. ( 2017-05-07 17:04:15 +0200 )edit ## 2 Answers Sort by » oldest newest most voted doesn't matter, i have found my own solution more ## Comments Hello petterson, please post your solution so that it might be of help to other people with this issue. ( 2017-05-06 19:52:55 +0200 )edit For enthusiasts: A way to convert$-amounts adorned with abbreviations concerning magnitudes to usable numerics.

If you study this demo based on a LibO using a locale with the comma as the decimal separator, you need to SUBSTITUTE the decimal points first.

In fact the kind of proceeding is much more valuable if adapted to technical (scientifical) tasks.
The funny one-cell-formulae should be replaced with a structured solution using helper columns, of course.

more