• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

separating a text column into three

S

scottgoldcom

New Member
Hi all:

I am trying to take large excel text cells and split them into no more than 3 cells. I have a limit of 30 characters per "new" cell and I really need the "breaks" to occur at word breaks. I am actually trying to replace the decriptions of products that our company sells with manufacturer supplied descriptions. They do it all in one cell and obviously I have three smaller cells that I need to fill. In some cases their descritpions are even longer than the 3 cells combined but I am fine with cutting them off after 3 cells times 30. Is there a somewhat easy way to do this, I have looked at Text to column but it doesn't appear to be able to address both the fixed columns and the word breaks at the same time.


Thanks a million!!
 
Good day scottgoldcom

[pre]
Code: 
I have used text to column to split one cell to four, three to keep and one rubbish cell to delete
[/pre]
https://dl.dropbox.com/u/75495784/Splitcells.xlsx
 
Hi bobhc:

Thanks, that helps with how to handle cells that are too long but I think that it still leaves me with the issue of splitting up words. I am hoping to keep words complete when splitting up the cell.


Thoughts??
 
scottgoldcom

If it is just one continuous piece of text with no spaces or commas and the words and syllables are of different length then I can not give you an answer. I used the text to column but the first two syllables were of the same length and I just set the last at an abattoir position to trim the end

`

Wait awhile and I am sure a Nija will come to help you
 
Thanks for your help, regardless. Here is an example of a line that I would like to separate into 3 cells:


LC/SC 8 METER 9um DUPLEX PATCH CORD YELLOW SINGLE MODE PLENUM LBL/LBL


Thanks again!


Scott
 
Please also mention the output you want.....it will be better if you give 2-3 more diversified examples or if you have a list of word, that will help in solution.


Regards,
 
Kuldeep:


Thanks for the response. I would like the output to be in Excel as well. In regards to a few more examples, here are some additional ones:


19in DOUBLE-SIDED NON-VENTED DIVIDED SHELF 2 RMS 25in DEPTH. BLACK


7ft CABINET 45 RMS 19in RACK MOUNT EQUIPMENT 27.5in WIDTH 36in DEPTH. BLACK


1.75in to .75in reducer -TSR3 to TSR1- Office White


LC FAST Connector 50um Multimode 900um 2.0mm 3.0mm Style


Hope that helps!!


Scott
 
Scott,


As i understand, These are the input strings....We need to know what will be the value in 3 cell. Please also explain that whether space will be there as seen in above input strings...


Regards,
 
If, for example, Cell A1 had the following long description:

19in DOUBLE-SIDED NON-VENTED DIVIDED SHELF 2 RMS 25in DEPTH. BLACK


My goal would be to have it broken down like so (with a max of 30 characters per cell and without the words being split):


Cell B1:

19in DOUBLE-SIDED NON-VENTED


Cell C1:

DIVIDED SHELF 2 RMS 25in


Cell D1:

DEPTH. BLACK


I hope that helps!!
 
NARAYANK991 A work of art

Any chance you could explain how it works,how do you select the start and stop of a selection. You have different length of words and number of words in different cells
 
Narayan


That looks like what I am trying to do!! I have a ton of Excel lines that I need to convert and that looks great if I can put each line in a different cell. I certaily appreciate your help on this!!


Thanks again.


Scott
 
Hi Scott ,


Good to know that you can sort out your problem.


@Bob :


I think it is all a tribute to the power of array formulas.


The starting point is the construct ROW(INDIRECT(....)) ; what this does is create an array of numbers , whose start and end points are defined within the inner brackets , shown as ....


Suppose we have the formula : =ROW(INDIRECT("1:10"))


What this does is create , in computer memory ( not on the worksheet ) , an array of numbers from 1 through 10. To see this , just enter the above formula in any cell. The cell will display 1 ; however , if you place your cursor on that cell , and press F2 , F9. You will see the following :


={1;2;3;4;5;6;7;8;9;10}


To further see the power of this , create a range name one_to_ten ( or any other name ! ) and in the Refers To box , type in the above formula =ROW(INDIRECT("1:10"))


Now , on the worksheet , in any cell , put in the formula : =SUM(one_to_ten)


You will see 55 , which is the sum of the numbers from 1 through 10.


I'll break this up here , and continue it in the next post.


Narayan
 
Hi Bob ,


To continue ...


So , now , we have a way of examining every character in the cell ; this is done by using the MID function. The syntax for this is MID(text,start_character,number_of_characters)


If we use the formula =MID("Excel",one_to_ten,1) in any cell , place the cursor in that cell and press F2 , F9 , you will see the array : ={"E";"x";"c";"e";"l";"";"";"";"";""}


You will see that after the last character "l" in the word Excel , the remaining characters are "" ; this because we defined one_to_ten as a list of numbers from 1 to 10. Now suppose we modify the ROW(INDIRECT(....)) construct a little bit , and have something like this :


=MID("Excel",ROW(INDIRECT("1:"&LEN("Excel"))),1)


Going through the same steps , you will now see the following : ={"E";"x";"c";"e";"l"}


The unnecessary characters are no longer present , since the array is only as long as the length of the word "Excel". You can also put in the word "Excel" in any cell , and give the cell reference in the above formula.


(To be contd. )


Narayan
 
Hi Bob ,


In Scott's case , we don't need to examine each character using MID , even though we could have used it and seen whether the resulting character is a SPACE. An easier way is to use the SEARCH function. The syntax for this is SEARCH(find_text,within_text,[start_point]).


find_text is the text we are looking for ; in this case , it will be a SPACE character " ".


within_text is the text within which we will be looking for the SPACE character ; if the entire text string is in cell A1 , within_text will be A1.


start_point is the place , within the text string , from where to begin the search. start_point is enclosed in square brackets to signify that this parameter is optional ; if you don't specify it , Excel starts the search from the first character.


A simple SEARCH formula like =SEARCH(" ","Excel is amazing") will return 6 , since the first SPACE character in the string "Excel is amazing" is in the 6th position.


A more complicated SEARCH formula , such as :


=SEARCH(" ","Excel is an amazing software package",ROW(INDIRECT("1:"&LEN("Excel is an amazing software package"))))


will display the value 6 , but if you press F2 , F9 , you will see this :

[pre]
Code: 
={6;6;6;6;6;6;9;9;9;12;12;12;20;20;20;20;20;20;20;20;29;29;29;29;29;29;29;29;29;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
[/pre]
The digits are repeating because from the first character till the sixth character in the string , the first space encountered is in the 6th position ; from the 7th character till the 12th character , the next space encountered is in the 12th position , and so on.


The last 7 #VALUE! errors are because beyond the 29th position , there are no more space characters in the text string.


(To be contd. )


Narayan
 
Hi Bob ,


Carrying on from an earlier post ....


Now that we are able to get the sequence of values representing the various positions where spaces are to be found in the original text , we need to know when the pre-defined limit of 30 is crossed ; I have rephrased this question as "What is the maximum position where a space occurs , which is less than the pre-defined limit of 30 ?"


It looks very straightforward in English , but we run into problems when we try to implement this in code !


When you add the condition "<30" to the SEARCH function , as shown below , using our earlier example :

[pre]
Code: 
=SEARCH(" ","Excel is an amazing software package",ROW(INDIRECT("1:"&LEN("Excel is an amazing software package"))))<10
pressing F2 , F9 , shows :

[pre][code]={TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
You see that what this returns is an array of TRUE , FALSE values. Along with this , we also have the error values #VALUE!.


Eliminating the error values is a simple matter of wrapping an IFERROR function around the SEARCH function as in :

=IFERROR(SEARCH(" ","Excel is an amazing software package",ROW(INDIRECT("1:"&LEN("Excel is an amazing software package")))),0)<10
[/pre]
Here again , you have two options ! In case of an error , should you have a low value ( 0 ) or a high value ( 999 ) ?


With the above low value , you get the following :

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}[/code][/pre]
where the last few error values have been replaced by TRUE.


If , instead , you had used the high value ( 999 ) , the last few error values would have been replaced by FALSE.


(To be contd.)


Narayan
 
Hi Bob ,


We are almost there !


When we use the SUMPRODUCT function , we are familiar with the construct =SUMPRODUCT((range=criterion)*(range)) , where the range=criterion portion returns an array of TRUE / FALSE values ; multiplying this by another range of numeric values returns an output array of numeric values , where those values which do not satisfy the criterion will be 0.


The same principle is used here ; having got an array of TRUE , FALSE values , we multiply this array by an array of numbers 1 , 2 , 3 ,.... , which we can get using the ROW(INDIRECT(...)) construct , as follows :


=(IFERROR(SEARCH(" ","Excel is an amazing software package",ROW(INDIRECT("1:"&LEN("Excel is an amazing software package")))),0)<10)*(ROW(INDIRECT("1:"&LEN("Excel is an amazing software package"))))


The above formula will give you the result :


={1;2;3;4;5;6;7;8;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;30;31;32;33;34;35;36}


As long as the position of the SPACE character was less than 10 , the multiplication yields the numbers , and beyond that , since the condition is FALSE , which is equivalent to 0 , multiplication results in all zeros. The numbers 30 , 31 , ... are because the IFERROR wrapper returned TRUE instead of the error values.


It is clear that we cannot use the low value ( 0 ) within the IFERROR wrapper ; what happens if we use the high value ( 999 ) ?


We get :


={1;2;3;4;5;6;7;8;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}


Perfect ! This what we want ; now we can use the MAX function to get the highest value from this array.


The last step is straightforward ; initially , we use the SEARCH function starting from 1 ; every subsequent SEARCH should start from the place where we got the last SPACE character + 1 ; in our example , since we get the highest position as 9 ( "Excel is " ) , we start our next SEARCH from the 10th position.


Last , but not least , since all of these are supposed to operate on , and return arrays , we need to enter our completed formula as an array formula , using CTRL SHIFT ENTER.


Narayan


P.S. I just noticed that if you use 999 as the error value for the IFERROR wrapper , there is a problem in the final string segment ; use LEN(text_string) instead of this.
 
Narayan:


All I can say is WOW. Thank you for the time and your willingness to share this. I was thinking that there was a simple"command" I could use but you went way beyond what I had ever hoped. Your work is incredible.


Thanks again.


Scott
 
Good day NARAYANK991

`

Thank you for taking the time and undoubted trouble to explain your formula/function.

There are times when functions and formulas are posted but they are over the heads

of mere mortals such as my self. This is one to copy, save, and study with the hope of learning, again I thank you.
 
Narayan


I have posted your formula and functions to drop box, I would ask if you could spare some of you valuable time to check it before everyone downloads and saves to their excel library

`

https://dl.dropbox.com/u/75495784/The%20problem_split%20in%20three.docx
 
NARAYANK991 It is not for you to ask for time it so for us to wait with patience and baited breath
 
You must log in or register to reply here.
Back
Top