Break up info in 1 cell to multiple cells on another worksheet

[Andrewryan3]

I need to break up/spread out the information from cell J in worksheet #1 to cells CS through CX on worksheet #2. The information in cell J is similar to the following:

'For resolution unit 6/5/17 regarding activation fees for video group plus, Brandon PLUS negotiated savings $24,272.66 and fee billing is 21% below market and fee schedule savings'

Each cell in CS-CX can hold up to 100 characters (including spaces). The information in 'J' varies at any given time, so I am clueless where to even start

Thanks

 

[Rick Rothstein]

So total maximum of 6 columns = 600 potential characters?, max 100 characters per column?
Do you simply want the string from column J to be spread evenly over the 6 columns? If so, what is the minimum string length you want in each column? CS-CX?
Let's assume 20 is the minimum

In CS =IF(LEN($J2)<=20,$J2,LEFT($J2,MAX(20,LEN($J2)/600)))
In CT =IF(LEN($J2)<=20,"",MID($J2,LEN($CS2)+1,MAX(20,LEN($J2)/600)))
In CU dragover to CX =IF(LEN($J2)<=20,"",MID(CT2,LEN(CT2)+1,MAX(20,LEN($J2)/600)))

This will put minimum 20 characters in each column until J-string distributed across CS-CX and maximum 100.

I am pretty sure the OP wants the text broken on the space rather than mid-word.

 

[Rick Rothstein]

Rick, good morning!
I tested this on a cell that had 25 characters. This worked well, however it put 85 characters in the first column, 100 in the second and 25 in the 3rd. Is there a way to evenly distribute the info? I am just wondering since this prints on a receipt for the customer and my boss has asked to make it look more uniform. Thank you very much for what you have already done. I think you are amazing.

I assumed you wanted the text broken on the spaces and not mid-word. The only reason there would only be 85 characters on the first (or any line) is if the next word (plus any attached punctuation) were 15 or more characters in length (because you said there was a maximum 100 characters per line)... the reason the next line would have 100 characters is because they fit exactly within your 100 characters maximum per line. As for your question about balancing the text so each line is relatively equal in length... that is not as simple a programming assignment as you might think. Let me ponder on it for a bit and see if I can come up with something for you.

 

[Roderick_E]

I assumed you wanted the text broken on the spaces and not mid-word. The only reason there would only be 85 characters on the first (or any line) is if the next word (plus any attached punctuation) were 15 or more characters in length (because you said there was a maximum 100 characters per line)... the reason the next line would have 100 characters is because they fit exactly within your 100 characters maximum per line. As for your question about balancing the text so each line is relatively equal in length... that is not as simple a programming assignment as you might think. Let me ponder on it for a bit and see if I can come up with something for you.

Good deal -- I leave this thread up to the expert. Thanks

 

[Andrewryan3]

So total maximum of 6 columns = 600 potential characters?, max 100 characters per column?
Do you simply want the string from column J to be spread evenly over the 6 columns? If so, what is the minimum string length you want in each column? CS-CX?
Let's assume 20 is the minimum

In CS =IF(LEN($J2)<=20,$J2,LEFT($J2,MAX(20,LEN($J2)/600)))
In CT =IF(LEN($J2)<=20,"",MID($J2,LEN($CS2)+1,MAX(20,LEN($J2)/600)))
In CU dragover to CX =IF(LEN($J2)<=20,"",MID(CT2,LEN(CT2)+1,MAX(20,LEN($J2)/600)))

This will put minimum 20 characters in each column until J-string distributed across CS-CX and maximum 100.

thank you

 

[Andrewryan3]

I assumed you wanted the text broken on the spaces and not mid-word. The only reason there would only be 85 characters on the first (or any line) is if the next word (plus any attached punctuation) were 15 or more characters in length (because you said there was a maximum 100 characters per line)... the reason the next line would have 100 characters is because they fit exactly within your 100 characters maximum per line. As for your question about balancing the text so each line is relatively equal in length... that is not as simple a programming assignment as you might think. Let me ponder on it for a bit and see if I can come up with something for you.

Actually after relooking at all of it, and testing it as a printed receipt in a test environment, it looks great. No need to fix.