Have large sheet with values listed as abbreviated numbers
I would like to find an easy way to convert them.
EXAMPLE: Cell contents are
2.9K
32.5K
16M
Want to convert to
2900
32500
16000000
or a way to use original values in mathematical expression
10*15K
[Solved] Need to change 2.9k into 2900
[Solved] Need to change 2.9k into 2900
Last edited by RonR1965 on Sat Sep 07, 2019 9:22 am, edited 1 time in total.
open office version 4.1.5
Windows 10
Windows 10
Re: Need to change 2.9k into 2900
Use the menu Edit -> Find & Replace and search for K and replace it with E3. For M, change the Replace With value to E6. You may want to select the range of cells that you want to affect and set the Current Selection Only option in the More Options section of the Find & Replace dialog
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Need to change 2.9k into 2900
Ok, now cell went from 2.9K to 2.9E3 I can go to cell and put an = sign in front of 2.9E3 and get error but formula in cell has =2900 if I go back and delete = sign it remains 2900 like I want. but that doubles the work as I have manually go to each cell twice.
open office version 4.1.5
Windows 10
Windows 10
Re: Need to change 2.9k into 2900
If this is a singular task, do as suggested by FJCC.
If it's reoccurring frequently, you might be tempted to automate the solution with the help of custom code. Just for fun I created a rather sophisticated solution last february in pursuit of a question in a different forum. It is primarily made for the direct conversion of newly entered content, but can also be used later. Ask for it again, if imterested.
The idea concerning formulae would surely not find a friend in the existing formula parser, but you can either write a user function for it or compose more complicated formulae to get the result. This again requires a strict definition (and limitation) of abbrevs to use. See attached example. (For friends of trický formulae only.)
Anyway: The abbreviations you are talking of as suffixes to numbers are only standardised as prefixes to SI-units. There the abbreviation for the factor 10^3 is "k" (lower case). That's not just finickiness. The meaning "M" and "m" in the same context differs by a factor of 10^9.
If it's reoccurring frequently, you might be tempted to automate the solution with the help of custom code. Just for fun I created a rather sophisticated solution last february in pursuit of a question in a different forum. It is primarily made for the direct conversion of newly entered content, but can also be used later. Ask for it again, if imterested.
The idea concerning formulae would surely not find a friend in the existing formula parser, but you can either write a user function for it or compose more complicated formulae to get the result. This again requires a strict definition (and limitation) of abbrevs to use. See attached example. (For friends of trický formulae only.)
Anyway: The abbreviations you are talking of as suffixes to numbers are only standardised as prefixes to SI-units. There the abbreviation for the factor 10^3 is "k" (lower case). That's not just finickiness. The meaning "M" and "m" in the same context differs by a factor of 10^9.
- Attachments
-
- aoo94262ordersOfMagbitudeAbbreviated_1.ods
- (10.52 KiB) Downloaded 85 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need to change 2.9k into 2900
@RonR1965 - When I did the Find & Replace the first cell appearance changed to 2.90E+003 and behaved as a number. If that is not happening for you, it may be due to the cell formatting. My cells are formatted as Number - General (as shown in the menu Format -> Cells).
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Need to change 2.9k into 2900
Despite my cautionary comment concerning "tricky formulae" there were some undismayed downloads of the demo.
Therefor I also supply the enhanced version here.
Therefor I also supply the enhanced version here.
- Attachments
-
- aoo94262ordersOfMagbitudeAbbreviated_2.ods
- (24.06 KiB) Downloaded 77 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München