Written by Allen Wyatt (last updated January 3, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Kirk needs to search for things like "yd2" and replace it with "yd2" where the "2" is superscripted. He wonders if there is a way to do that in Excel.
The find and replace capabilities of Excel are more limited than those of Word, where such replacements are relatively easy. While you could export your information to Word, do the replacements, and then import it back into Excel, there are some things you can do without ever leaving Excel.
First, however, let's examine something that you might reasonably think would work, but doesn't really. Note that the Replace tab of the Find and Replace dialog box seems to provide a way to specify attributes for the text you want to use as the replacement. This might lead you to think that you could do the following:
While this sounds good in theory, it won't work. You can follow the steps, including making sure that the replacement 2 is set to be superscript. The problem, however, is that Excel applies the superscript format to the entire cell, not just to the 2. Thus, you end up with yd2 completely as superscript.
You could, if you wanted, skip superscripting all together and just use a typeface character that appears superscripted. If you use the Symbol dialog box, you can find the digits 0 through 3 that appear superscripted. If you use the superscripted digit 2 (ASCII 178) in your replacement text, then you can get the desired appearance. Follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Finally, if you really want to use superscripts, your best bet is going to be using a macro to do the formatting. The simplest method is tied to the specific example provided—making the 2 in yd2 superscript.
Sub DoConvert() Dim c As Range For Each c In Selection.Cells If c.Value = "yd2" Then c.Characters(3, 1).Font.Superscript = True End If Next End Sub
To use the macro, select the cells you want to modify, then run the macro. Each cell in the selection is stepped through and checked to see if it contains the text yd2. If it does, then the third character (the 2) is made superscript; the rest of the cell is undisturbed.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12605) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Superscripts in Find and Replace.
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!
Do you often want to search through a worksheet by column rather than by row? Excel defaults to searching by row, of ...
Discover MoreWildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...
Discover MoreWhen doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-01-03 15:18:06
Ronmio
A similar but more versatile technique is to use Insert Symbol. There are hundreds of unique symbols (e.g., the many dozens of symbols in Segoe UI Symbol, Webdings, Wingdings, etc. ) that can be part of a replacement. However, because currently you cannot insert symbols directly into the "Replace with", you need to use an interim step. First, you want to insert the desired symbol into a blank cell and copy (Ctrl+C) the character(s) from the FORMULA BAR of that cell and then insert (Ctrl-V) it/them into the replacement text.
Life would be a little simpler if Office would allow you to Insert Symbols into dialog boxes. Microsoft are you listening?
2020-11-21 10:28:25
Rick Rothstein
The macro you posted assumes the yd2 is in a cell by itself. What if the column is not a Units column though, but rather a Description column instead where, for each cell, the yd2 text could appear one or more times embedded within other text? An example could be, "I ordered 100 yd2 but they delivered 105 yd2 by mistake". Here is a macro that will work for either a Units column or for a Description column..
Sub ydSquared()
Dim Position As Long, Cell As Range
For Each Cell In Selection
Position = InStr(1, Cell.Value, "yd2", vbTextCompare)
Do While Position > 0
If Mid(Cell.Value & " ", Position + 2) Like "2[!A-Za-z0-9]*" Then
Cell.Characters(Position + 2, 1).Font.Superscript = True
End If
Position = InStr(Position + 1, Cell.Value, "yd2", vbTextCompare)
Loop
Next
End Sub
2018-05-26 15:20:04
Excellent Tip Allen. Thank you.
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