Manipulate a String - Vba solution

[stuartgb100]

Hi,

Trying to help a friend with this problem:

They have masses of essentially text data which is basically a specialist English to French dictionary.
A cell contains both the English word(s) and the French equivalent, and he wishes to split this into two cells.

Looking through the data it seems that the English expression is always in bold text, but not always just alphabetic characters.

So I'm looking for a way to split everything bold in the cell from everything not bold.

A couple of examples:

anti-backflow throat (fireplace)^ gorge anti-refoulante f
beam,^ prestressed concrete poutre précontrainte f; poutrelle céramique f; poutrelle béton f

where '^' represents the end of the bold section

I've also got to deal with French accents

Any ideas please ?

Thanks.

 

[mole999]

is each entry on a new line (english french)
if the ^ exists then text to column

 

[Peter_SSs]

Looking through the data it seems that the English expression is always in bold text, ..

A couple of examples:

anti-backflow throat (fireplace)^ gorge anti-refoulante f
beam,^ prestressed concrete poutre précontrainte f; poutrelle céramique f; poutrelle béton f

where '^' represents the end of the bold section

Did you make a mistake with marking the end of the bold text in the second example as the red text looks very English to me.

 

[Peter_SSs]

Assuming the translation data is in column A, starting on row 2, and the results can go in columns B:C then try this on a small set of sample data.

Sub SplitBold()
  Dim a As Variant
  Dim i As Long, j As Long, lr As Long, chrs As Long
  Dim bUnbold As Boolean
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  ReDim a(2 To lr, 1 To 2)
  For i = 2 To lr
    bUnbold = False
    With Cells(i, 1)
    chrs = Len(.Value)
      j = 0
      Do
        j = j + 1
        If .Characters(j, 1).Font.Bold = False Then bUnbold = True
      Loop Until bUnbold Or j = chrs
      If Not bUnbold Then j = j + 1
      a(i, 1) = Trim(Left(.Value, j - 1))
      a(i, 2) = Trim(Mid(.Value, j))
    End With
  Next i
  With Range("B2:C2").Resize(UBound(a) - 1)
    .Value = a
    .Columns.AutoFit
  End With
End Sub

My sample data & results:

Book1
	A	B	C
2	abc	abc
3	anti-backflow throat (fireplace)gorge anti-refoulantef	anti-backflow throat (fireplace)	gorge anti-refoulantef
4
5	beam, prestressed concretepoutre prcontraintef; poutrelle cramiquef; poutrelle btonf	beam,	prestressed concretepoutre prcontraintef; poutrelle cramiquef; poutrelle btonf
6	fsfasfd		fsfasfd
7	abc	ab	c
Eng Fr

 

[stuartgb100]

Thanks Peter.
I'll try it on more data.

 

[stuartgb100]

Okay, so having tried it on a larger sample of data, I've mixed results.
It may be that the problems will have to be sorted out with a variety of 'cleaning' routines being run individually on problem cells.
On the other hand, it might be that the code could be modified to run some 'cleaning' first, before the split ?

At random I have chosen 4 original examples that caused problems, together with the results after the split.
In every case after conversion, all text becomes unboldened.

nail, extra large head clout clou à tête plate, ‘extra large’ m pin, dome-headed upholstery pointe décorative à tête bombée f pine pin m; (from Landes region of France) pin des Landes m plane, hand/jack rabot manuel m; ~ semelle metal m the English split: nail, pin, dome-headed pine plane, hand/jack the French split: extra large head clout clou à tête plate, ‘extra large’ m upholstery pointe décorative à tête bombée f pin m; (from Landes region of France) pin des Landes m rabot manuel m; ~ semelle metal m These are direct copy/pastes from the problem cell, so hopefully any errors caused by the import into Excel will be preserved in this post. If not, I could post a worksheet. Any further help would be much appreciated. <colgroup><col></colgroup><tbody> </tbody> <strike></strike> <colgroup><col></colgroup><tbody> </tbody> <strike></strike> <colgroup><col></colgroup><tbody> </tbody> <strike></strike>

<colgroup><col></colgroup><tbody>
</tbody>

<strike></strike>

 

[Peter_SSs]

The reason the existing code is not doing what you want, relates to the 'messy' nature of your data.
The existing code looks from the start of the string until it finds a non-bold character (or the end of the string if no bold found) and splits at that point.

In that first sample line in your last post, the blank character between the comma and the word 'extra' is not bold, hence my code split it at that point.
There are similar situations with those other sample texts as well. In fact, you can clearly see in the third example that there is bold text then non-bold then bold then non-bold.

How would it be if we went the other way and started looking from the end of the string back towards the beginning & split as soon as we come to a bold character?

Sub SplitBold_v2()
  Dim a As Variant
  Dim i As Long, j As Long, lr As Long, chrs As Long
  Dim bBold As Boolean
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  ReDim a(2 To lr, 1 To 2)
  For i = 2 To lr
    bBold = False
    With Cells(i, 1)
    chrs = Len(.Value)
      If chrs > 0 Then
      j = chrs + 1
        Do
          j = j - 1
          If .Characters(j, 1).Font.Bold = True Then bBold = True
        Loop Until bBold Or j < 2
        If bBold Then j = j + 1
      
        a(i, 1) = Trim(Left(.Value, j - 1))
        a(i, 2) = Trim(Mid(.Value, j))
      End If
    End With
  Next i
  With Range("B2:C2").Resize(UBound(a) - 1)
    .Value = a
    .Columns.AutoFit
  End With
End Sub

Book1
	A	B	C
2	abc	abc
3	anti-backflow throat (fireplace)gorge anti-refoulantef	anti-backflow throat (fireplace)	gorge anti-refoulantef
4
5	beam, prestressed concretepoutre prcontraintef; poutrelle cramiquef; poutrelle btonf	beam,	prestressed concretepoutre prcontraintef; poutrelle cramiquef; poutrelle btonf
6	fsfasfd		fsfasfd
7	abc	ab	c
8	nail,extra large head cloutclou tte plate, extra largem	nail,extra large head clout	clou tte plate, extra largem
9	pin, dome-headedupholsterypointe dcorative tte bombef	pin, dome-headedupholstery	pointe dcorative tte bombef
10	pinepinm;(from Landes region of France)pin des Landesm	pinepinm;(from Landes region of France)	pin des Landesm
11	plane, hand/jackrabot manuelm; ~ semelle metalm	plane, hand/jack	rabot manuelm; ~ semelle metalm
Eng Fr (2)