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.
Note:
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 MoreReplacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain ...
Discover MoreThe Find and Replace feature in Excel is one of the workhorse editing tools you can use. When the Find and Replace dialog ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-07-21 08:00:04
SteveJez
John,
If you just select the text box or shape you can format as much as you like, font, size, colour, alignment. The only draw back is the whole box or shape has the same formatting. If you need a different format for a title or the like, create another text box, format that as you like, align the text boxes & group them together. This works in Excel 2007 & 365 so presumably everything in between.
HTH
2019-07-20 15:25:48
John
Well Now - that's something I just learned - using a formula to place the text in a cell into a text box. After playing around with it for a while, I have found one problem - I cannot select the text in the text box to format it (size, colour, weight, font, etc.), and formaing the text in the source cell doesn't follow through to the representation in the text box. This is true whether I select the text within the source cell or the cell iteself for formating. The only way I have found, so far, is to use some of the tools on the Format tab on the ribbon, such as the word art gallery, and this provides no obvious way to change the size of the font. I haven't experimented with charts, to see if the same holds true there. My esperiments were done with Excel 2010 in Windows 10
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 © 2021 Sharon Parq Associates, Inc.
Comments