How do I use string values in a cell to do calculations?

[Jowaco]

Thanks in advance for help with this. I have a very large spreadsheet, 20,000+ rows, with a list of chemical formulas and I would like to calculate the molecular weights based on the chemical formula. For example, the cell contains the string "CH4" (without the quotes) and I would like to use this information to calculate the molecular weight by adding the mass of 1 carbon (C) and 4 hydrogens (H), or 12.0107 + (4 x 1.0079) = 16.0423. I can certainly do the equations, but I have no clue as to how I pull out the information in a string in a cell. It can get very complicated, such as C7H3ClF3NO2, with some of the constituents having 2 letters, like Cl, and some having only 1, as well as components with only one atom present are not followed with a 1, like N, but those with more than one atom, like C, will have the count, 7, listed after the symbol.

Thanks again!:confuse

[shg]

Like https://www.lenntech.com/calculators...calculator.htm ar https://www.bocsci.com/molecular-weight-calculator.html ?

[Jowaco]

That is exactly what I want to do, but I don't have time to do it 20,000 times.

[Glenn Kennedy]

Try this:

https://excelxor.com/2016/03/07/molecular-weights/

XOR LX, one of our regular gurus loves these complex formulae, as you will see when you D/L the sheet...

[Jowaco]

Thanks!!! Now I just have to figure out how to manipulate this to calculate everything in my list.

[shg]

Someone might be able to help you automate Explorer to do that.