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.
