Forum Discussion
Converting data into millions
Hi, I am working with a dataset where the wages are recorded in £M or £K. I was wondering how I would convert all the numbers into £M. For example, if the account was £8.5M. It would remain at 8.5M. However, if it were £12K, it would convert to 0.012M.
I first tried converting the data from text to columns to eliminate the £ sign. I am unsure how to convert all the wages to M. Any help would be appreciated.
=IF(RIGHT(C7,1)="M",C7,"£"&MID(C7,2,LEN(C7)-2)/1000&"M")You can try this formula for the data layout of the example.
- Hi, sorry what would this lead to? I am just a beginner so not exactly sure what the outcome of this would be
See the attached sample workbook. It demonstrates OliverScheurich 's approach and and alternative.
- If cell has numbers then simply divide by million.
1000000
Before After
1000000 1
20000 0.02
300000 0.3
First type 1000000 in any cell , then copy the cell. Select the range where to paste value
Click on Home >> Paste>> Paste Special >>Operation Click on Divide
Selected range will be converted to Millions.- They are in the format of £11M or £8K. Not in actual numbers so I don't if what you have suggested would work
- Ok, Use find and replace with values, CTRL + H
For getting convert to 1M = 1000000
In find value what type m
replace with 000000
Click replace all, M is replaced with 000000
same way for K 1K=1000
In find value what type k
replace with 000
1k will become 1000
make sure that based on text just give decimal value
