Converting Text within a cell to multiplier

**Greenfire** · 11-18-2009, 11:57 PM

I'm not very experienced with excel, not beginner but certainly not pro, so any help would really be appreciated.

I have numbers mixed in with a letter which I would like to replace with a certain multiplier. To give an example, I start off with the following in the cell: "100K". I'd like to replace the K with "x1000" so that it turns the cell into 100000. If it'd be possible to include some code that would also convert "m" to "x1000000" that would be absolutely wonderful.

Having one piece of code search for K and M in the cell and then replace it as necessary would be best.

Thanks in advance!

**Paul** · 11-19-2009, 12:33 AM

Hi Greenfire, and welcome to the forum.

Would a formula suffice?

=IF(ISNUMBER(FIND("K",UPPER(G1))),SUBSTITUTE(UPPER(G1),"K","")*1000,IF(ISNUMBER(FIND("M",UPPER(G1))),SUBSTITUTE(UPPER(G1),"M","")*1000000,G1))

Where G1 contains a number, or a number with a K or an M in it.