Splitting up Bold fonts

[Manolocs]

Hello, Column A list containing first or second word in BOLD the rest is normal font.
Need split in column B the words in BOLD and column C the rest of the phrase.
Any help?

 

[Peter_SSs]

OK, as I understand it, you are looking at the first two words only for (at most) one that is bold, to go to column B
Anything else in column A goes to column C.

You could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formulas as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function SplitBold(r As Range, Optional bBold As Boolean = True) As String
  Dim i As Long
  Dim bBoldFound As Boolean
  
  For i = 1 To InStr(InStr(1, r.Value & " ", " ") + 1, r.Value & "  ", " ") - 1
    If r.Characters(i, 1).Font.Bold Then
      bBoldFound = True
      If bBold Then
        SplitBold = Mid(r.Value, i, InStr(i, r.Value & " ", " ", 1) - i)
      Else
        SplitBold = Left(r.Value, i - 1) & Mid(r.Value, InStr(i, r.Value & " ", " ", 1) + 1)
      End If
      Exit For
    End If
  Next i
  If Not bBoldFound And Not bBold Then SplitBold = r.Value
End Function

My Excel jeanie screen shot below will not display partial bold font within a cell so the words that are bold in my sheet are the UPPER case ones below.

Excel Workbook
	A	B	C
1	Data	Bold	Not Bold
2	a BIG book	BIG	a book
3	CAR	CAR
4
5	boat		boat
6	RED apples are nice	RED	apples are nice
7	a big red APPLE		a big red APPLE
Split Bold

 

[Manolocs]

Hi Peter, in this list normally the first word is Bold, but there are some lines that I have the first two to first 3 words in Bold, What I need is the Bold words in column B and the normal fonts in column C. sorry for the lack of information.

 

[Peter_SSs]

Try this version then. If the first letter of the word is bold then the word goes into the Bold column, otherwise into the Not Bold column.

Function SplitBold(r As Range, Optional bBold As Boolean = True) As String
  Dim i As Long, pos As Long
  Dim Words As Variant
  Dim sBold As String, sUnbold As String
  
  If r.Cells.Count = 1 And Len(r.Cells(1).Value) Then
    Words = Split(r.Value)
    pos = 1
    For i = 0 To UBound(Words)
      If r.Characters(pos, 1).Font.Bold Then
        sBold = sBold & " " & Words(i)
      Else
        sUnbold = sUnbold & " " & Words(i)
      End If
      pos = pos + Len(Words(i)) + 1
    Next i
    If bBold Then
      SplitBold = Mid(sBold, 2)
    Else
      SplitBold = Mid(sUnbold, 2)
    End If
  End If
End Function

Once again the upper case words below are the bold ones.
In the formulas you can use 1 in place of TRUE and 0 in place of FALSE if you want.

Excel Workbook
	A	B	C
1	Data	Bold	Not Bold
2	a BIG book	BIG	a book
3	CAR	CAR
4
5	fishing boat		fishing boat
6	RED apples are nice	RED	apples are nice
7	a big red APPLE	APPLE	a big red
8	ALL WORDS BOLD	ALL WORDS BOLD
9	EVERY second WORD is BOLD	EVERY WORD BOLD	second is
10
Split Bold

 

[Manolocs]

Hello Peter, works perfect thank you very much for your time and explanation, by the way it works in Excel for Windows and Mac as well.

 

[Peter_SSs]

Hello Peter, works perfect thank you very much for your time and explanation, by the way it works in Excel for Windows and Mac as well.

Good news on both accounts.