Ask Your Question

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

asked 2017-05-06 14:11:13 +0200

petterson gravatar image

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

edit retag flag offensive 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.)

Lupp gravatar imageLupp ( 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.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-07 17:04:15 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-06 18:09:07 +0200

petterson gravatar image

doesn't matter, i have found my own solution

edit flag offensive delete link more


Hello petterson, please post your solution so that it might be of help to other people with this issue.

librebel gravatar imagelibrebel ( 2017-05-06 19:52:55 +0200 )edit

answered 2017-05-06 23:24:50 +0200

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-06 14:11:13 +0200

Seen: 39 times

Last updated: May 06 '17