Excel Chart: replace first line of data label caption

Question

In a pie of pie chart the last point is named "Other". I'd like to rename it to "abc".

The data labels have three lines:

• category
• value
• percentage

How do you replace only the first line of a caption and keep the rest of it as it is?

This does replace "Other" with "abc", but it also deletes value and percentage:

Dim k As Long
Dim j As Long
With ActiveChart
    For k = 1 To .SeriesCollection.Count
        For j = 1 To .SeriesCollection(k).Points.Count
            With .SeriesCollection(k).Points(j).DataLabel
                'Rename "Other"
                If .Caption Like "Other" & vbLf & "*" Then
                    .Caption "abc"
                End If
            End With
        Next j
    Next k
End With

This code by Tim Williams replaces the chart title:

Sub tester()

    'ReplaceTitle ActiveSheet.ChartObjects(1).Chart, "ghj", "fffffff"
    ReplaceTitle ActiveChart, "F", "fffffff"

End Sub

Private Sub ReplaceTitle(cht As Chart, ReplaceWhat As String, ReplaceWith As String)
    Dim sTitle As String, pos
    If cht.HasTitle Then
        pos = InStr(cht.ChartTitle.Characters.Text, ReplaceWhat)
        If pos > 0 Then
            cht.ChartTitle.Characters(pos, Len(ReplaceWhat)).Text = ReplaceWith
        End If
    End If
End Sub

Can this be modified to work with data labels?

Answer 1

• Option 1: Split the caption by vbLf and replace the first part
• Option 2: Replace the Other with abc

Dim k As Long
Dim j As Long, aTxt
With ActiveChart
    For k = 1 To .SeriesCollection.Count
        For j = 1 To .SeriesCollection(k).Points.Count
            With .SeriesCollection(k).Points(j).DataLabel
                'Rename "Other"
                If .Caption Like "Other" & vbLf & "*" Then
                    ' Option 1
                    aTxt = Split(.Caption, vbLf)
                    aTxt(0) = "abc"
                    .Caption = Join(aTxt, vbLf)
                    With .Format.TextFrame2.TextRange
                        .Font.Bold = True
                        .Characters(Len(aTxt(0) & aTxt(1)) + 2, Len(aTxt(2))).Font.Bold = False
                    End With
                    ' Option 2
'                    .Caption = Replace(.Caption, "Other", "abc")
                End If
            End With
        Next j
    Next k
End With