Split bold from normal text

[bczwy]

Hi everyone,

I hope anyone can help me with this issue. I am not very expert on coding with VBA but I'm trying to search for a code that splits BOLD text from non bold text.

All my texts are looking like this:
[FONT=&quot]1: Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

I got like 500 of these sort textes and I would like to have the bold part in a column before.

Help is appreciated a lot!

Thanks in advance.[/FONT]

 

[Rick Rothstein]

All my texts are looking like this:
[FONT="]1: Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

I got like 500 of these sort textes and I would like to have the bold part in a column before.
[/FONT]

Some questions...

1) All that text is in a single cell, correct?

2) What column are your text cells in? If not Columns A, is the column before the text empty?

3) When you say 'all my text are looking like this", does that mean the bold text is always and only at the beginning of the text?

4) Did you want to keep the moved text bold or do you want it to become normal text after the move?

 

[bczwy]

Hi Rick Rothstein,

Please see the replies below:

Some questions...

1) All that text is in a single cell, correct?

Yes all text is in a single cell

2) What column are your text cells in? If not Columns A, is the column before the text empty?
It doesn't matter yet actually because I am copying them from word documents, so I can place them in any column I like, A, B, C it doesn't matter for me, what is easier for you.

3) When you say 'all my text are looking like this", does that mean the bold text is always and only at the beginning of the text?

I have actually had a double checking and I noticed a lot of the numbers are not in bold and then the words behind it are in bold and then none bold.

So I got two sort of texts:
1: Excel This is an excel document
2: Word This is an word document
But I also found a VBA script which can make the first word in bold, so I think the primary focus is on having number two.

4) Did you want to keep the moved text bold or do you want it to become normal text after the move?

That doesn't matter too tbh, but it needs to be split and not copy pasted.

Thanks a lot for your help Rick, It's appreciated!

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SplitAtBold()
  Dim X As Long, Cell As Range
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    For X = InStr(Cell.Value, ":") + 1 To Len(Cell.Value)
      If Cell.Characters(X, 1).Text Like "[! " & Chr(160) & "]" And Not Cell.Characters(X, 1).Font.Bold Then
        Cell.Characters(X - 1, 1).Text = Chr(1)
        Exit For
      End If
    Next
  Next
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]

 

[bczwy]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SplitAtBold()
  Dim X As Long, Cell As Range
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    For X = InStr(Cell.Value, ":") + 1 To Len(Cell.Value)
      If Cell.Characters(X, 1).Text Like "[! " & Chr(160) & "]" And Not Cell.Characters(X, 1).Font.Bold Then
        Cell.Characters(X - 1, 1).Text = Chr(1)
        Exit For
      End If
    Next
  Next
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Rick,

Am I doing something wrong? I got the error:

"Run-time error '1004':
Unable to set the Text property of the Characters class

 

[Rick Rothstein]

Thanks Rick,

Am I doing something wrong? I got the error:

"Run-time error '1004':
Unable to set the Text property of the Characters class

Which line of code was highlighted for that error?

Also, can you show me the text that was in the cell when the error occurred?

And show me the value of X when the error occurred.

 

[bczwy]

Which line of code was highlighted for that error?

Also, can you show me the text that was in the cell when the error occurred?

And show me the value of X when the error occurred.

I have added one of the files to my drive and made the file public, it's an excel file: https://drive.google.com/file/d/17U0SpXJBGXduz3rsJexdOIEhQqrII8HK/view?usp=sharing

The error was all I received, it didn't give me any location unfortunately.

 

[ZVI]

Hi,
Give this a try:

Sub SplitByEndOfBold()
' ZVI:2017-11-21 https://www.mrexcel.com/forum/excel-questions/1032185-split-bold-normal-text.html
' Splitting cells by their bold font ending
  Dim a, i&, j&, s$
  s = Chr$(1)
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value(11) = Replace(.Value(11), "<" & "/B" & ">", "<" & "/B" & ">" & s)
    a = .Value
    If Not IsArray(a) Then ReDim a(1 To 1, 1 To 1): a(1, 1) = .Value
    For i = 1 To UBound(a)
      j = InStr(a(i, 1), s)
      If j = 0 Then
        a(i, 1) = s & a(i, 1)
      Else
        a(i, 1) = Replace(a(i, 1), s & " ", s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ".", "." & s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ")", ")" & s, 1, 1, 0)
      End If
    Next
    .Value = a
    .TextToColumns , xlDelimited, , , False, False, False, False, True, s
    .Font.Bold = False
    .Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

It's fast and has been successfully tested on your data.

Regards

 

[bczwy]

Hi,
Give this a try:

Sub SplitByEndOfBold()
' ZVI:2017-11-21 https://www.mrexcel.com/forum/excel-questions/1032185-split-bold-normal-text.html
' Splitting cells by their bold font ending
  Dim a, i&, j&, s$
  s = Chr$(1)
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value(11) = Replace(.Value(11), "<" & "/B" & ">", "<" & "/B" & ">" & s)
    a = .Value
    If Not IsArray(a) Then ReDim a(1 To 1, 1 To 1): a(1, 1) = .Value
    For i = 1 To UBound(a)
      j = InStr(a(i, 1), s)
      If j = 0 Then
        a(i, 1) = s & a(i, 1)
      Else
        a(i, 1) = Replace(a(i, 1), s & " ", s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ".", "." & s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ")", ")" & s, 1, 1, 0)
      End If
    Next
    .Value = a
    .TextToColumns , xlDelimited, , , False, False, False, False, True, s
    .Font.Bold = False
    .Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

It's fast and has been successfully tested on your data.

Regards

ZVI, the code works absolutely flawless. I thank you!

 

[bczwy]

Hi,
Give this a try:

Sub SplitByEndOfBold()
' ZVI:2017-11-21 https://www.mrexcel.com/forum/excel-questions/1032185-split-bold-normal-text.html
' Splitting cells by their bold font ending
  Dim a, i&, j&, s$
  s = Chr$(1)
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value(11) = Replace(.Value(11), "<" & "/B" & ">", "<" & "/B" & ">" & s)
    a = .Value
    If Not IsArray(a) Then ReDim a(1 To 1, 1 To 1): a(1, 1) = .Value
    For i = 1 To UBound(a)
      j = InStr(a(i, 1), s)
      If j = 0 Then
        a(i, 1) = s & a(i, 1)
      Else
        a(i, 1) = Replace(a(i, 1), s & " ", s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ".", "." & s, 1, 1, 0)
        a(i, 1) = Replace(a(i, 1), s & ")", ")" & s, 1, 1, 0)
      End If
    Next
    .Value = a
    .TextToColumns , xlDelimited, , , False, False, False, False, True, s
    .Font.Bold = False
    .Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

It's fast and has been successfully tested on your data.

Regards

Hi Zwy I just had one more question.

The split between bold and non bold is being made beautifully. But it seems like sometimes like maybe about 30-50% of my documents that I am using the script on the non bold text is also being split in multiple columns. Do you might have any idea what that is happening?

Thanks once again!