Forum Discussion
Changing 1.2M to 1,200,000 in Excel VBA
Hi everyone I would like to display: $1.2M as 1,200,000 & 120K as $120,000 as well as -1.2M as -$1,200,000 & -120K as -$120,000 for a range of cells I Know that the opposite can be done with [>...
I don't think you can achieve this be just reformatting since the imported "numbers" like $1.2M or 120K are actually texts. The formula below strips the Dollar-sign and the "M" or "K" from the text, converts it to a value and then multiplies it by either 1000000 or 1000, depending on "M" or "K". Assume the "number" you wish to rewrite is in A1.
=(RIGHT(A1)="M")*VALUE(MID(A1,2,LEN(A1)-2))*1000000+(RIGHT(A1)="K")*VALUE(MID(A1,2,LEN(A1)-2))*1000Now you can set the number format to your liking.