Hello, I hope someone can help me, I'm not sure if what I want to do is possible.

I have a big spreadsheet which pulls stock information from the web. There is a tab for each company, and a front page which I have rolled the info I need into. On that front page I need to use some of the data in formulas, however, one of the sets of data is giving me problems.

I have pulled the number of outstanding shares, and when the data is displayed it adds an "M" to the number, so instead of showing 442,000,000 it will show 442M. I can't use this in a formula because of the "M" so I'm wondering (okay, hoping really really bad!) if there is any way to remove the pesky "M", either by changing the data range properties where I'm bringing the data in from the web or by formatting, or something in the formula itself.

Again, I'm not sure this is possible, so I'd appreciate any ideas. Thank you!!

Hi,

try this formulae, use replace function to M with "000,000". Here is the example assuming you have 44M in A1
=REPLACE(A1,3,1,"000,000")

all the best

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!

Just out of curiosity, is there something that can be put into this function to locate any alpha character?

Example would be a single colomn with 1a,2b,3c...ect. as values and return 1,2,3...ect.?


Just wondering!

TD