Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
how to separate words between blanks | Excel Discussion (Misc queries) | |||
How do I separate words in one cell into two cells? | Excel Worksheet Functions | |||
how to separate words between blanks | Excel Discussion (Misc queries) | |||
Separate last word in cell with more than 2 words? | Excel Worksheet Functions |