split string by font and font size

Question

Hi all,

In A1 I have text that consists of words which all are separated by a space (and nothing else).

The words are in font Arial 12, Arial 11 ore Arial 9 (in this case no other fonts or font sizes).

From A5 down I need all Arial 12 words only, from B5 down I need all Arial 11 words only and from C5 down I need all Arial 9 words only.

If the split function is used all words are converted to my default font and fontsize. 

Perhaps possible with do loops that check from space to next space like

with

if font=Arial and font.size=12 then

…

But I can't find correct code, so your assistance will be appreciated very much.

Jack Sons.

Answer 1

I ran the code Rick, but it only put all text from A1 in C5, see below.

Below that result I showed how the text looks in Word, with the non printing characters (formatting marks) showing, maybe they cause the problem?

The only thing I can think of is that after you copied the text into cell A1, the things you think are spaces between words are not actually space characters (ASCII 32). Just a guess, but they may be non-breakable spaces (ASCII 160). Try copying the text from Word without the formatting marks showing and see if that makes a difference. If not, then see it this fixes things....

Sub SplitOutArialNineElevenAndTwelve()

  Dim X As Long, A As Long, B As Long, C As Long, NextSpace As Long

  Dim Result As Variant, Txt As String, Words() As String

  Txt = Replace(Range("A1").Value, Chr(160), " ")

  ReDim Result(1 To 1 + Len(Txt) - Len(Replace(Txt, " ", "")), 1 To 3)

  Words = Split(Txt)

  For X = 0 To UBound(Words)

    NextSpace = InStr(NextSpace + 1, " " & Txt, " ")

    Select Case Range("A1").Characters(NextSpace, 1).Font.Size

      Case 12: A = A + 1: Result(A, 1) = Words(X)

      Case 11: B = B + 1: Result(B, 2) = Words(X)

      Case 9: C = C + 1: Result(C, 3) = Words(X)

    End Select

  Next

  Range("A5").Resize(UBound(Result), 3) = Result

End Sub

Answer 2

I ran the code Rick, but it only put all text from A1 in C5, see below.

Below that result I showed how the text looks in Word, with the non printing characters (formatting marks) showing, maybe they cause the problem?

Jack.

Answer 3

Give this macro a try...

Sub SplitOutArialNineElevenAndTwelve()

  Dim X As Long, A As Long, B As Long, C As Long, NextSpace As Long

  Dim Result As Variant, Txt As String, Words() As String

  Txt = Range("A1").Value

  ReDim Result(1 To 1 + Len(Txt) - Len(Replace(Txt, " ", "")), 1 To 3)

  Words = Split(Txt)

  For X = 0 To UBound(Words)

    NextSpace = InStr(NextSpace + 1, " " & Txt, " ")

    Select Case Range("A1").Characters(NextSpace, 1).Font.Size

      Case 12: A = A + 1: Result(A, 1) = Words(X)

      Case 11: B = B + 1: Result(B, 2) = Words(X)

      Case 9: C = C + 1: Result(C, 3) = Words(X)

    End Select

  Next

  Range("A5").Resize(UBound(Result), 3) = Result

End Sub

Answer 4

See your thread list of words relating to font and font size. I tested the macro that I posted there; it works as intended. It is easy to adapt it for more font sizes.