split the contents of a cell

[spunkyjon]

Hi,

I would like to split the contents of a cell into two other cell,
however I am unsure if it is possible in this case.

Here is an example of what I have in each cell:

English word German word

All the cells are the same, with the english word followed by the
German word, The German word is written in bold font and the English
word is not. Is there any way of splitting cells by using the bold
text? There is no other delimeters such as commas in the cells. Also
the words are different lengths and some phrases of 2 or 3 words are
also included

Thanks in advance.

 

[Nigel]

The following illustrates an approach, the test uses the characters method
to read the font style for each character in the original word string. The
two words are allocated to sWord1 and sWord2, which I show in a MsgBox but
they could be assigned to two cells in your worksheet. I added a check in
case there are no bold characters found. The code processes one cell (A1)
but you will want to process a range using a loop I suspect.


Sub SplitWord()
Dim xC As Integer, sWord1 As String, sWord2 As String

With Cells(1, 1)
For xC = 1 To Len(Trim(.Value))
If .Characters(Start:=xC, Length:=1).Font.FontStyle = "Bold" Then
Exit For
End If
Next xC

If xC < Len(Trim(.Value)) Then
sWord1 = Mid(.Value, 1, xC - 1)
sWord2 = Mid(.Value, xC, Len(Trim(.Value)) - xC + 1)
MsgBox sWord1 & vbCrLf & sWord2
Else
sWord1 = "": sWord2 = ""
MsgBox .Value & " - no bold characters!"
End If
End With
End Sub

 

[spunkyjon]

I was hoping there was a way to do this without using macros.

What I would like is for the words to be split between two other
cells. So the current two words in A1 will be split between cells B1
(English word) and C1 (German word).

Thanks for the quick reply.

 

[Nigel]

I do not think you can do this using Formula, you could create a UDF and
apply this to cells.

 

[spunkyjon]

Ok, thanks for your help Nigel!

 

[Nigel]

Here is a UDF that does what you need, put it into a standard code module.

To use it, if original text is in A1

Put the following formula in cell B1 and C1 respectively

= splitw(A1,"English")
=splitw(A1,"German")

You actually only need to enter "E" or "G" in the above rather than the full
language name.



Function SplitW(myCell As Range, myLang As String) As String
' takes the value in myCell and strips the left part non-bold
' and right part bold text, returning either the left or right
' part
Dim xC As Integer
myLang = UCase(Left(myLang, 1))
With myCell
For xC = 1 To Len(Trim(.Value))
If .Characters(Start:=xC, Length:=1).Font.FontStyle = "Bold" Then
Exit For
End If
Next xC

If xC < Len(Trim(.Value)) Then
If myLang = "E" Then SplitW = Mid(.Value, 1, xC - 1)
If myLang = "G" Then SplitW = Mid(.Value, xC, Len(Trim(.Value)) - xC +
1)
Else
SplitW = myCell.Value
End If
End With
End Function

 

[spunkyjon]

Ok, thanks for your help Nigel!