separate whole words

[norika]

I want to separate the following words into two rows. As the length of each word is different, so i could not use 'text to coumns' function.
for example:
Word1 : the board of directors of abc company limited (len = 45) in cell u13
Word2 : the board of directors of abcdefgh company limited (len =50) in cell u14

I use the formula to separate word 1 into two rows: return #VALUE
1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16

But i use same formula to separate word2 into two rows : successful
1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18

Answer : 1st row : the board of directors of abcdefgh company
2nd row : limited
what is the problem?

TIA

norika

[Stefi]

Hi Norika,

The problem is that there are no spaces in string "the board of directors of
abc company limited" after its 40th character (41th-45th characters being
"mited") that's why FIND(" ",U13,40) returns #VALUE.

I'm still thinking on an appropriate solution!

Regards,
Stefi


„norika” ezt �*rta:

>
> I want to separate the following words into two rows. As the length of
> each word is different, so i could not use 'text to coumns' function.
> for example:
> Word1 : the board of directors of abc company limited (len = 45) in
> cell u13
> Word2 : the board of directors of abcdefgh company limited (len =50) in
> cell u14
>
> I use the formula to separate word 1 into two rows: return #VALUE
> 1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
> 2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
>
> But i use same formula to separate word2 into two rows : successful
> 1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
> 2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
>
> Answer : 1st row : the board of directors of abcdefgh company
> 2nd row : limited
> what is the problem?
>
> TIA
>
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[Stefi]

Hi Norika,

This can be a solution (in cell U15):
=IF(LEN(U13)<40,U13,LEFT(U13,FindRev(U13," ")))

where FindRev is an UDF as follows:

Public Function FindRev(StrtoSearch As String, StrSearchedFor As String) As
Long
FindRev = 0
On Error Resume Next
FindRev = InStrRev(StrtoSearch, StrSearchedFor)
End Function

Regards,
Stefi

„norika” ezt �*rta:

>
> I want to separate the following words into two rows. As the length of
> each word is different, so i could not use 'text to coumns' function.
> for example:
> Word1 : the board of directors of abc company limited (len = 45) in
> cell u13
> Word2 : the board of directors of abcdefgh company limited (len =50) in
> cell u14
>
> I use the formula to separate word 1 into two rows: return #VALUE
> 1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
> 2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
>
> But i use same formula to separate word2 into two rows : successful
> 1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
> 2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
>
> Answer : 1st row : the board of directors of abcdefgh company
> 2nd row : limited
> what is the problem?
>
> TIA
>
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[bpeltzer]

Varying lengths suggest you cannot use the 'fixed width' option of Text to
Columns. But how about the delimited version, with the space character as
the delimiter?

"norika" wrote:

>
> I want to separate the following words into two rows. As the length of
> each word is different, so i could not use 'text to coumns' function.
> for example:
> Word1 : the board of directors of abc company limited (len = 45) in
> cell u13
> Word2 : the board of directors of abcdefgh company limited (len =50) in
> cell u14
>
> I use the formula to separate word 1 into two rows: return #VALUE
> 1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
> 2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
>
> But i use same formula to separate word2 into two rows : successful
> 1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
> 2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
>
> Answer : 1st row : the board of directors of abcdefgh company
> 2nd row : limited
> what is the problem?
>
> TIA
>
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[norika]

Stefi.
It does not work for me. Also, i dont understand your UDF(user defined function) how to work. Would you mind to explain in details.

bpeltzer,
Using text to column function with space, it only seperates words by words. It could not divide into two rows. Except this, is there any ideas?

Anyway, thany you for your help.

norika

[Stefi]

Norika,
First create the UDF: Tools/Macro/Visual Basic/Insert/Module
Copy the code in the Module1 window

Now you find the UDF among your functions in the User defined category.

The function returns the place of the first space from the end of the string
backwards, e.g. FindRev(U13," ") returns 38


Hope it will work!

Regards,
Stefi

„norika” ezt �*rta:

>
> Stefi.
> It does not work for me. Also, i dont understand your UDF(user defined
> function) how to work. Would you mind to explain in details.
>
> bpeltzer,
> Using text to column function with space, it only seperates words by
> words. It could not divide into two rows. Except this, is there any
> ideas?
>
> Anyway, thany you for your help.
>
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[norika]

Stefi,
Thank your for help. I found that the UDF can count the length of phrase except last word. Actually, the phrase is the payee printed in cheque. If the length is too long, it will cut into two. From your UDF, it only cuts out the last word.
Is there any ideas to solve it if the max length of 1st row is 40, more than that it will write in 2nd row?

TIA
norika

[Stefi]

Hi Norika,

Try this UDF:

Public Function CutToPieces(StrToCut As String, CutAt As Integer, PartNo As
Integer) As String
Dim PieceArray1() As String
Dim PieceArray2() As String
PieceArray1 = Split(StrToCut, , -1)
darab1 = UBound(PieceArray1)
darab2 = 0
strpiece1 = Join(PieceArray1)
strpiece2 = ""
Do While Len(strpiece1) > CutAt
ReDim Preserve PieceArray2(darab2)
For d2 = 0 To darab2 - 1
PieceArray2(d2 + 1) = PieceArray2(d2)
Next d2
PieceArray2(0) = PieceArray1(darab1)
darab1 = darab1 - 1
ReDim Preserve PieceArray1(darab1)
strpiece1 = Join(PieceArray1)
strpiece2 = Join(PieceArray2)
darab2 = darab2 + 1
Loop
CutToPieces = IIf(PartNo = 1, strpiece1, strpiece2)
End Function

Use it like this:

in cell u15
= CutToPieces(U13, 40, 1)
in cell u16
= CutToPieces(U13, 40, 2)

Regards,
Stefi


„norika” ezt �*rta:

>
> Stefi,
> Thank your for help. I found that the UDF can count the length of
> phrase except last word. Actually, the phrase is the payee printed in
> cheque. If the length is too long, it will cut into two. From your UDF,
> it only cuts out the last word.
> Is there any ideas to solve it if the max length of 1st row is 40, more
> than that it will write in 2nd row?
>
> TIA
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[norika]

Stefi,
Excellent! Thany you very much.

As I am not too familiar with UDF or marco, would you mind to explain the 'CutToPieces' how to work.

TIA
norika

[Ron Rosenfeld]

On Thu, 29 Dec 2005 02:47:19 -0600, norika
<[email protected]> wrote:

>
>I want to separate the following words into two rows. As the length of
>each word is different, so i could not use 'text to coumns' function.
>for example:
>Word1 : the board of directors of abc company limited (len = 45) in
>cell u13
>Word2 : the board of directors of abcdefgh company limited (len =50) in
>cell u14
>
>I use the formula to separate word 1 into two rows: return #VALUE
>1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
>2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
>
>But i use same formula to separate word2 into two rows : successful
>1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
>2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
>
>Answer : 1st row : the board of directors of abcdefgh company
>2nd row : limited
>what is the problem?
>
>TIA
>
>norika

If I understand you correctly, you are trying to place in the first row,
everything except the last word of the sentence; and in the second row just the
last word.

The problem is you have to find the last <space> in the sentence in order to do
this.

The following formulas will do this:

1st row: (in R15)

=LEFT(U13,-1+FIND(CHAR(1),SUBSTITUTE(U13," ",
CHAR(1),LEN(U13)-LEN(SUBSTITUTE(U13," ","")))))

2nd row (if 1st row is not in R15, change that reference in the formula below):

=TRIM(SUBSTITUTE(U13,R15,""))



--ron

[Stefi]

Hi Ron,

I think the problem is that Norika not always wants to find the last space!
E.g.
l is the 40th
character
the board of directors of abc company limited
is to be split like
the board of directors of abc company
limited

but n is the 40th character
the board of directors of abcdefghijklmno company limited
like
the board of directors of
abcdefghijklmno company limited


Regards,
Stefi

„Ron Rosenfeld” ezt �*rta:

> On Thu, 29 Dec 2005 02:47:19 -0600, norika
> <[email protected]> wrote:
>
> >
> >I want to separate the following words into two rows. As the length of
> >each word is different, so i could not use 'text to coumns' function.
> >for example:
> >Word1 : the board of directors of abc company limited (len = 45) in
> >cell u13
> >Word2 : the board of directors of abcdefgh company limited (len =50) in
> >cell u14
> >
> >I use the formula to separate word 1 into two rows: return #VALUE
> >1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
> >2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16
> >
> >But i use same formula to separate word2 into two rows : successful
> >1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
> >2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18
> >
> >Answer : 1st row : the board of directors of abcdefgh company
> >2nd row : limited
> >what is the problem?
> >
> >TIA
> >
> >norika
>
> If I understand you correctly, you are trying to place in the first row,
> everything except the last word of the sentence; and in the second row just the
> last word.
>
> The problem is you have to find the last <space> in the sentence in order to do
> this.
>
> The following formulas will do this:
>
> 1st row: (in R15)
>
> =LEFT(U13,-1+FIND(CHAR(1),SUBSTITUTE(U13," ",
> CHAR(1),LEN(U13)-LEN(SUBSTITUTE(U13," ","")))))
>
> 2nd row (if 1st row is not in R15, change that reference in the formula below):
>
> =TRIM(SUBSTITUTE(U13,R15,""))
>
>
>
> --ron
>

[Stefi]

Hi Norika,

First here is a new version, I found a bug in the previous one (see
operation logic in comments):

Public Function CutToPieces(StrToCut As String, CutAt As Integer, PartNo As
Integer) As String
Dim PieceArray1() As String 'Declare two arrays (PieceArray1 and
PieceArray2) for the elements of the two strings (1st and 2nd part)
Dim PieceArray2() As String
PieceArray1 = Split(StrToCut, , -1) 'split original string to words
separated by spaces
darab1 = UBound(PieceArray1) 'count the number of words in array1
darab2 = 0 'count the number of words in array2
strpiece1 = Join(PieceArray1) 'put together result string1 from words
strpiece2 = ""
Do While Len(strpiece1) > CutAt 'check and iterate if length of result
string1 is longer than predefined number
ReDim Preserve PieceArray2(darab2) 'if yes, declare array2 one-word
longer
For d2 = darab2 To 1 Step -1 'cycle shifts words in array2
towards the end
PieceArray2(d2) = PieceArray2(d2 - 1)
Next d2
PieceArray2(0) = PieceArray1(darab1) 'transfer next word from
array1 to array2
darab1 = darab1 - 1 'decrease count of words in array1
ReDim Preserve PieceArray1(darab1) 'decrease number of words in
array1
strpiece1 = Join(PieceArray1) 'put together result string1 from
words
strpiece2 = Join(PieceArray2) 'put together result string2 from
words
darab2 = darab2 + 1 'increase number of words in array2
Loop
CutToPieces = IIf(PartNo = 1, strpiece1, strpiece2) 'return result
depending on which part was required
End Function


Regards,
Stefi



„norika” ezt �*rta:

>
> Stefi,
> Excellent! Thany you very much.
>
> As I am not too familiar with UDF or marco, would you mind to explain
> the 'CutToPieces' how to work.
>
> TIA
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496595
>
>

[Ron Rosenfeld]

On Tue, 3 Jan 2006 01:41:02 -0800, "Stefi" <[email protected]>
wrote:

>Hi Ron,
>
>I think the problem is that Norika not always wants to find the last space!
>E.g.
> l is the 40th
>character
>the board of directors of abc company limited
>is to be split like
>the board of directors of abc company
>limited
>
>but n is the 40th character
>the board of directors of abcdefghijklmno company limited
>like
>the board of directors of
>abcdefghijklmno company limited
>
>
>Regards,
>Stefi
>

OK, I read some of her other notes than the first and understand that what she
really wants to do is limit each line to no more than forty characters, still
breaking at <space>. However, that is contrary to her initial example, which
actually had more than 40 characters in a line.

However, this can still be easily done with worksheet functions:

Assumption is that we will require no more than two lines:

R13:

=LEFT(LEFT(U13,40),-1+FIND(CHAR(1),SUBSTITUTE(LEFT(U13,40)," ",
CHAR(1),LEN(LEFT(U13,40))-LEN(SUBSTITUTE(LEFT(U13,40)," ","")))))

R14:

=TRIM(REPLACE(U13,1,LEN(R13),""))


--ron