# Converting data into millions

Occasional Contributor

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

16 Replies

# Re: Converting data into millions

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.

# Re: Converting data into 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

# Re: Converting data into millions

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

# Re: Converting data into millions

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

# Re: Converting data into millions

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

# Re: Converting data into millions

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

# Re: Converting data into millions

Thank you. I'll check it out

# Re: Converting data into millions

I tried both approaches but still a bit confused. I can send a small example through private message

# Re: Converting data into millions

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.

# Re: Converting data into millions

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

# Re: Converting data into millions

@Taii36 But does it work now?

# Re: Converting data into millions

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

# Re: Converting data into millions

Could you send a file that demonstrates where it fails?

# Re: Converting data into millions

See the attached version.

# Re: Converting data into millions

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

# Re: Converting data into millions

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.