Seperating Cell contents according to font

**jordan2322** · 01-05-2012, 06:33 AM

Hi Everyone,

I've got text in cells that is as follows

Column A
Excel Microsoft
Microsoft Excel
etc

Is there a way i can seperate this column into columns B and C.
i.e. I want the Bolded values in column B and the non bold in column C

Column A                                Column B               Column C
Excel Microsft                          Excel                  Microsoft 
Microsoft Excel                         Microsoft              Excel

Cheers

**Kyle123** · 01-05-2012, 07:57 AM

Is the data always in this format? Couldn't you just use text to columns on the space?

**Kelshaer** · 01-05-2012, 11:05 AM

Try This Code

Sub SplitBold()

Dim i As Long
Dim c As Integer
For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    For c = 1 To VBA.Len(Range("A" & i))
        If Range("A" & i).Characters(c, 1).Caption <> " " Then
            If Range("A" & i).Characters(c, 1).Font.FontStyle = "Bold" Then
                Range("B" & i) = Range("B" & i) & Range("A" & i).Characters(c, 1).Caption
                Range("B" & i).Font.Bold = True
            Else
                Range("C" & i) = Range("C" & i) & Range("A" & i).Characters(c, 1).Caption
            End If
        End If
    Next
Next

End Sub