Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
norika
 
Posts: n/a
Default separate whole words


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default separate whole words

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
norika
 
Posts: n/a
Default separate whole words


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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
norika
 
Posts: n/a
Default separate whole words


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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
norika
 
Posts: n/a
Default separate whole words


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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default separate whole words

On Thu, 29 Dec 2005 02:47:19 -0600, 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


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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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
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

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stefi
 
Posts: n/a
Default separate whole words

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


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default separate whole words

On Tue, 3 Jan 2006 01:41:02 -0800, "Stefi"
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Excel files in separate sessions, not just separate windows? Bob at Dexia Design Excel Discussion (Misc queries) 1 October 18th 05 05:46 PM
how to separate words between blanks Elizabeth Excel Discussion (Misc queries) 1 July 16th 05 09:55 PM
How do I separate words in one cell into two cells? Vanessa Excel Worksheet Functions 3 June 8th 05 02:32 PM
how to separate words between blanks Mexage Excel Discussion (Misc queries) 0 May 13th 05 12:03 AM
Separate last word in cell with more than 2 words? Pat Mayton Excel Worksheet Functions 2 March 24th 05 04:37 PM


All times are GMT +1. The time now is 01:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"