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

MarcRNelson

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.

Mike 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

 

MarcRNelson

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

Mike Wilday

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