Quote:
Originally Posted by brooklyn040204
Thank you! I am working with the formula, I think it's going to work - well, I'm going to make it work. I didn't realize until this morning that the data has two decimal places before the "M" so I'm trying to use the replace formula twice on the same cell to replace the decimal point with a comma, and then come in to replace the "M" with the appropriate number of zeros. If that's not possible I am going to have to roll this data into a few columns, replacing what I need to as I go. You've got me on the right track, thanks so much!
|
I've got it! I wasn't going to be able to tell how many digits were going to be in each cell as I refreshed the data, which also made me realize that I couldn't replace the M with a specific number of zeroes, as if it added one too many we would be putting off all of the numbers. I included a find function to the formula you gave me so that instead of me telling it where the "M" might be it now looks for the "M" and replaces it with nothing:
=REPLACE($L3,FIND("M",$L3),1,"")
So now I've turned this: 494.74M
Into this: 494.74
And it works whether the number is 44.25 million or 325.5 million, and I can just designate that the column represents millions.
Thanks again, I couldn't have gotten this far without some help, or without working on this spreadsheet for the rest of my life!