Find and extract/copy bolded portions of a string in a cell

[hfearing]

In an Excel spreadsheet, I have cells that contain text in a combination of regular and bold font. I would like to have a function that allows me to look at a cell and copy out the portion of the string that is bold.

For example, if cell H5 contained:

F Nx A

I would like to have a user-defined function GetBold(H5) that would look in cell H5 and return "Nx".

Thanks for your help.

 

[JoeMo]

In an Excel spreadsheet, I have cells that contain text in a combination of regular and bold font. I would like to have a function that allows me to look at a cell and copy out the portion of the string that is bold.

For example, if cell H5 contained:

F Nx A

I would like to have a user-defined function GetBold(H5) that would look in cell H5 and return "Nx".

Thanks for your help.

If you want the output in bold font, format the cells you place this formula in as bold.

Function GetBold(R As Range)
Dim i As Long, S As String
For i = 1 To R.Characters.Count
    If R.Characters(i, 1).Font.Bold = True Then S = S & Mid(R.Value, i, 1)
Next i
If S = "" Then
    GetBold = ""
Else
    GetBold = S
End If
End Function

 

[hfearing]

That works for me - thanks for your help!

 

[JoeMo]

That works for me - thanks for your help!

You are welcome - thanks for the reply.

 

[Rick Rothstein]

A slightly different approach with a slightly different result if there are non-contiguous bolded text in the cell. For example, if this is in the cell...

If AAA and BBB Then

JoeMo's code returns AAABBB whereas my code below will return AAA BBB (with a single space separating each non-contiguous bolded text. If you only have a single word bolded, then both our codes return the same thing.

Function GetBold(R As Range) As String
  Dim X As Long
  GetBold = R(1).Value
  For X = 1 To Len(GetBold)
    If Not R(1).Characters(X, 1).Font.Bold Then Mid(GetBold, X) = " "
  Next
  GetBold = Application.Trim(GetBold)
End Function

 

[hfearing]

thanks