Written by Allen Wyatt (last updated November 30, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Ruby is trying to find an easy way to determine the number of atoms in molecular formulas of some chemical structures. For instance, a cell might contain a formula such as C12H10N6F2. In this case the number of atoms is 12 + 10 + 6 + 2 = 30. Ruby has about 300 of these formulas to do and was wondering if there is an Excel formula that can be used to do this.
First, the bad news: There is no easy way to do this.
There; with that out of the way, we can start to look for solutions. The example chemical formula provided by Ruby may lead some to think that counting atoms is a simple process of substituting the alphabetic characters with something else so that just the numeric characters can be evaluated. As an example, here is Ruby's example chemical formula:
C12H10N6F2
If you replace the alphabetic characters with plus signs, you get this:
+12+10+6+2
Looks like a simple formula now, right? This is deceiving, because while it will work in this instance, it may not work at all for Ruby's other chemical formulas. Consider the following chemical formula that many people will be familiar with:
H2O
Doing the same substitution renders this:
+2+
Problem is, there is an implied count of 1 whenever there is a single element—for example, the oxygen element. Thus, H20 is actually 3 atoms.
So now we can come up with a way to simply account for the implied 1, right? Sure; this can be done. It can be done most easily and cleanly with a macro, such as the following user-defined function:
Function CountAtoms(ChemForm As String) Dim sNewNum As String Dim sTemp As String Dim iNewAtoms As Integer Dim iTotalAtoms As Integer Dim J As Integer sNewNum = "" iTotalAtoms = 0 For J = 2 To Len(ChemForm) sTemp = Mid(ChemForm, J, 1) If sTemp >= "0" And sTemp <= "9" Then sNewNum = sNewNum & sTemp ElseIf sTemp <= "Z" Then iNewAtoms = Val(sNewNum) If iNewAtoms = 0 Then iNewAtoms = 1 iTotalAtoms = iTotalAtoms + iNewAtoms sNewNum = "" End If Next J iNewAtoms = Val(sNewNum) If iNewAtoms = 0 Then iNewAtoms = 1 iTotalAtoms = iTotalAtoms + iNewAtoms CountAtoms = iTotalAtoms End Function
In order to use this function in your worksheet, you would simply reference the chemical formula:
=CountAtoms(A1)
If the chemical formula is in cell A1, this function returns the count you desire. It will even work with formulas such as the following:
NaCl SbF6
Note that these rely on two-character element names, of which there are many. It does require, however, that the second character of a two-character element name not be capitalized.
So, will this approach work with all chemical formulas? Not really; it only works with the simple ones we've covered so far. You see, chemical formulas can get quite complex. Consider the following example:
2H2O
When an initial number appears like this, then the formula is to be multiplied by that value. Thus, instead of the normal 3 atoms in H2O, this formula would have 6 atoms.
It gets worse. Consider the following valid chemical formulas:
Ca3(PO4)2 Al2(SO4)3(H2O)18
Note the parentheses followed by a number. In this nomenclature, the value immediately following the closing parenthesis indicate how many of the molecules within the parentheses are in the larger molecule. Thus, in the second example there are 3 molecules of SO4 and 18 molecules of H2O in the overall molecule. This obviously affects the number of atoms in the entire formula. To compound complexity, parentheses can even be nested:
CH3(C3H4(NH2)2)18CH3
Fun, huh?
This can still be addressed with a more complex macro. Rather than reinvent the wheel here, though, if you are working with complex chemical formulas such as these, you might want to consider using the macros provided at this site:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=670
Note that the macros aren't implemented as user-defined functions. To use them you simply select the cells with the formulas, run the macro, and then the macros modify information in the columns to the right of the selected chemical formulas. Full instructions are included with the code at the above website.
You'll also need to make sure you enable, in the Visual Basic Editor, regular expressions. You do this by choosing Tools | References and then scrolling through the available references to locate the Microsoft VBScript Regular Expressions 5.5 option. Make sure the check box to the left of the reference is selected, then click OK.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13707) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Knowing if a workbook is already open can be a prerequisite to your macro working correctly. Here's how to check it out.
Discover MoreImport information from a program external to Excel, and your numbers may be treated as text because of the way that the ...
Discover MoreWhen programming macros, variables are used extensively. At some point you might want to exchange the values held in two ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-11-30 17:41:42
Leslie Glasser
Please note: The statement "Thus, in the second example there are 3 molecules of SO4 and 18 molecules of H2O in the overall molecule." is chemically incorrect. SO4 is not a molecule, rather call it "a group of atoms". Similarly, the overall formula does not represent a molecule but rather an "empirical formula".
2019-11-30 14:54:12
Philip
Numerical items in a molecule formula sometimes don’t relate to the number of atoms but to the relative position of the bond in the molecule ... simply counting them (even taking into account all the considerations mentioned above) still won’t give a robust solution ...
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments