Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding and Replacing in Text Boxes.
by Allen Wyatt
(last updated July 20, 2019)
David wonders if it is possible to use Find and Replace to locate and modify text in text boxes or in labels in charts. The short answer is that it is not possible, but there are several workarounds you can try.
First, you could easily make the text in your text boxes or in your chart labels dynamic, so that it is tied to the contents of some worksheet cells. For instance, you could do the following for your text boxes:
That's it. You can use the same technique with custom chart labels—all you need to do is select the chart label and enter a cell reference in the Formula bar. With the text boxes and chart labels tied to worksheet cells, you can easily use Find and Replace to search for and change information in the cells. When the changes are made, the text boxes and chart labels should automatically reflect the changes in the cells.
The only way to actually change the text within a text box or chart label is to change it manually or change it using a macro. The code would need to step through each text box in the worksheet and then make your change. The following is a simple version of a macro that can make such a change.
Sub TextBoxReplace() Dim shp As Shape Dim sOld As String Dim sNew As String 'Change as desired sOld = "Old string" sNew = "New string" On Error Resume Next For Each shp In ActiveSheet.Shapes With shp.TextFrame.Characters .Text = Application.WorksheetFunction.Substitute( _ .Text, sOld, sNew) End With Next End Sub
This macro steps through all the shapes in the worksheet (text boxes are shapes) and then replaces whatever is in the sOld variable with whatever is in the sNew variable. Applying the same technique to chart labels is only a bit more complex, as shown in the following macro:
Sub ChartLabelReplace() Dim Cht As ChartObject Dim Ser As Series Dim scPt As Point Dim sOld As String Dim sNew As String 'Change as desired sOld = "Old String" sNew = "New String" On Error Resume Next For Each Cht In ActiveSheet.ChartObjects For Each Ser In Cht.Chart.SeriesCollection For Each scPt In Ser.Points With scPt.DataLabel .Text = Application.WorksheetFunction.Substitute( _ .Text, sOld, sNew) End With Next Next Next End Sub
The macro steps through each data label for every data series on every chart and (again) replaces any instances of whatever is in sOld with whatever is in sNew.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9264) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Finding and Replacing in Text Boxes.
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!
Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...Discover More
Replacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain ...Discover More
The Find and Replace feature in Excel is one of the workhorse editing tools you can use. When the Find and Replace dialog ...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.