I have a large data set that I need to sum, but the amounts are written in K or M. Is there a way to change these into numerical values so that I can add them? Some of the values are entered as 1.5K or 32.79M, so find and replace with 0’s for K or M will not work. Thank you for any advice!
This seems to work. Find/Replace, first deal with the items with k:
In the Search for box, type ^([^k]+)k
In the Replace box, type =$1*1000
Turn on Regular expressions, then Replace all.
Then the same for M, with M instead of k in the search term, and 1000000 instead of 1000.
This worked great! Thank you!