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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...Discover More
Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of ...Discover More
Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning of ...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.