Written by Allen Wyatt (last updated September 19, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 had 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, 2013, 2016, 2019, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...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
Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...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.