Converting data into millions

Copper Contributor

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.

16 Replies
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

@Taii36 

=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.

converting data.JPG 

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


Hi, sorry what would this lead to? I am just a beginner so not exactly sure what the outcome of this would be

@Taii36 

See the attached sample workbook. It demonstrates @OliverScheurich 's approach and and alternative.

Thank you. I'll check it out
I tried both approaches but still a bit confused. I can send a small example through private message

@Taii36 

your previous example had £ (pound sterling), the new one has € (euro).

The attached version does not depend on the currency symbol. Converted Wage #1 shows the text values converted to numbers. Converted Wage #2 has the exact same formula, but a custom number format.

Because i did this with the original data set following the approach by Quraduple but the boxes remained empty

@Taii36 But does it work now?

Sort of. Some values worked, but it created blanks for others

@Taii36 

Could you send a file that demonstrates where it fails?

@Hans Vogelaar 

See the attached version.

Thank you. I was wondering if you covert the text to columns or skipped this step. I tried copying the formula but ended with an exponential answer like 1.1E+08

 

@Taii36 

I didn't use Text to Columns, the conversion is done by a formula.

If you get exponential notation, you may need to widen the column, and perhaps apply a custom number format.