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.
Thanks in advance
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.)
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.
doesn’t matter, i have found my own solution
Hello petterson, please post your solution so that it might be of help to other people with this issue.
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.