by Allen Wyatt
(last updated January 24, 2015)
Evan has a worksheet that contains a number of chemical compound notations, such as H2O. When he firsts enters the compound, he can format the 2 as a subscript, as it should be. However, Evan needs to search for instances where the 2 is not subscripted and replace it with the correctly subscripted notation. He wonders if there is an easy way to do this.
There is no way to do this using the Find and Replace features of Excel. The reason? As one ExcelTips subscriber put it, "Excel is spectacularly bad at handling rich text in cells." Evidence of this is the very fact that you cannot search for mixed formatting within a cell or replace with mixed formatting.
Note that I said "mixed formatting," which is what Evan wants—the "H" and "O" use different formatting than the "2". If Evan hand wanted to change the entire cell contents to regular text or to subscribe, then you could have used the regular Find and Replace tool. It won't work for mixed formatting, though.
This means that the best approach is to use a macro to do the finding and replacing. There are several ways you can approach this; the following is just one.
Sub SubscriptNumbers() Dim c As Range Dim sWord As String Dim sChar As String Dim x As Long For Each c In Selection sWord = c.Value For x = 1 To Len(sWord) sChar = Mid(sWord, x, 1) If sChar >= "0" And sChar <= "9" Then c.Characters(Start:=x, Length:=1).Font _ .Subscript = True End If Next x Next c Set c = Nothing End Sub
To use the macro, select the cells you want to modify and then run it. It steps through each cell in the selection and then examines the cell contents. If there are any digits in the contents, then those digits are formatted as subscript.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13362) applies to Microsoft Excel 2007, 2010, and 2013.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Want to use Excel's Find feature to locate cells based on what those cells display? It's easy if you know how to adjust ...Discover More
Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.Discover More
Wildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.