Aug 18 2022 07:50 AM
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.
Aug 18 2022 08:03 AM
Aug 18 2022 08:05 AM
Aug 18 2022 08:08 AM
=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.
Aug 18 2022 08:14 AM
Aug 18 2022 08:20 AM
Aug 19 2022 04:23 AM
See the attached sample workbook. It demonstrates @OliverScheurich 's approach and and alternative.
Aug 19 2022 04:59 AM
Aug 19 2022 05:46 AM
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.
Aug 19 2022 08:22 AM
Aug 19 2022 08:48 AM
Aug 19 2022 09:00 AM
Could you send a file that demonstrates where it fails?
Aug 19 2022 12:22 PM
See the attached version.
Aug 19 2022 12:28 PM - edited Aug 19 2022 12:37 PM
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
Aug 19 2022 12:46 PM
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.