Page 1 of 1
[Solved] Need to change 2.9k into 2900
Posted: Tue Jul 10, 2018 10:51 pm
by RonR1965
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
Re: Need to change 2.9k into 2900
Posted: Tue Jul 10, 2018 11:26 pm
by FJCC
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
Re: Need to change 2.9k into 2900
Posted: Wed Jul 11, 2018 12:17 am
by RonR1965
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.
Re: Need to change 2.9k into 2900
Posted: Wed Jul 11, 2018 12:24 am
by Lupp
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.
Re: Need to change 2.9k into 2900
Posted: Wed Jul 11, 2018 2:00 am
by FJCC
@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).
Re: Need to change 2.9k into 2900
Posted: Wed Jul 11, 2018 11:44 am
by Lupp
Despite my cautionary comment concerning "tricky formulae" there were some undismayed downloads of the demo.
Therefor I also supply the enhanced version here.