Search code examples
excelexcel-formulaformattingworksheet-function

Convert value of money

I can't seem to find this or I am not searching for the correct term. I am simply trying to change money to long string. The data being pulled to D cell has it as $7.5K for example. I would like to remove the dollar symbol and write the decimal to long form so change $7.5K to 7500.

A few notes: -ALL the data is above $1K so nothing will be below that amount. -money has no ONEs or TENs value so these are always 00.

Not sure if I can automate this or make it easier to convert these values using the Find/Replace.

Solution

  • If the true /underlying value is a number, simply reformat as Number with no decimal places. If a text string either:

    Replace K by *1000 and then $ by = and format to suit

    or, as suggested by @Ken White, apply a formula such as: 

    =1000*SUBSTITUTE(SUBSTITUTE(A1,"$",""),"K","")

    again with suitable formatting.