Adding a $ to a Reformatted Number ending in "M" (Millions)

MarcRNelsonMarcRNelson ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
03/15/18 Edited 12/09/19

Hi,

I am trying to convert a number in millions but shown as a thousand to the nearest decimal point and then add "M" to the end of the converted number.

I understand the formula to divide by 1000 and then use the ROUND function to get one decimal point,but when I concatenate the "M" at the end I loose my dollar sign.

Does any one know a work around for this?

Example:

So $2,150 is converted to $2.150 and then rounded to $2.2 then displayed as $2.2M

This needs to remain dynamic so when the data in the sheet is updated the number updates to the correct format.

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    Can you concatenate and add the dollar sign back in? 

    You should be able to use the + sign to add text to the string by adding "$"+[column name]24+"M"

    Would add $ + the contents of the cell in row 24 + M

     

  • MarcRNelsonMarcRNelson ✭✭✭✭✭

    Thank you Mike! Think I was over thinking this one. Appreciate your help.

  • Mike WildayMike Wilday ✭✭✭✭✭

    Been there, done that, will probably do it again in an hour! laugh Glad i could help.

Sign In or Register to comment.