Excel: How to split Bold text from normal text.

 

1. Create macro

You can open Macro editor by holding key ALT and F11 together on your keyboard.

Then create a new macro with these codes:

Function BoldEnds(r As Range) As Long

    ' returns the character position of the first non-bold character

    ' in the first cell of r

 

    Dim iUB As Long

    Dim iLB As Long

    Dim iMid As Long

 

    With r(1)

        If .HasFormula Or VarType(.Value) <> vbString Then

            BoldEnds = -1

 

        ElseIf IsNull(.Font.Bold) Then  ' some bold, some not

            iUB = Len(.Value)

            iLB = 0

 

            Do While iUB - iLB > 1

                iMid = (iLB + iUB) \ 2

                If .Characters(iMid, 1).Font.Bold Then

                    iLB = iMid

                Else

                    iUB = iMid

                End If

            Loop

 

            BoldEnds = iUB

        ElseIf .Font.Bold Then

            BoldEnds = Len(.Value) + 1

        Else

            BoldEnds = 1

        End If

    End With

End Function

Next step:

For example if your cell which have combined bold and normal text is in cell A1

Then in the Cell that you need to extract bold text to, you would put in this:

=LEFT(A1, BoldEnds(A1)-1)

And for the cell that you need to extract normal text to, you would put in this:

=TRIM(MID(A1, BoldEnds(A1), LEN(A1)))

Then you will get the all the bold text in one cell, and all the normal text in another cell.