Break up text in a single cell into separate cells with limited text length

[SUNNY ISLAND]

I have the following text in cell A1

HUNDRED TREES, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015

I would like to break up the text to cells A2 and A3 and each cell can take only text length up to 40

Can this be done via WS formula instead of VBA?

THANKS

 

[SUNNY ISLAND]

thanks much. appreciate it

 

[Peter_SSs]

thanks much. appreciate it

You are welcome.

 

[Peter_SSs]

@ Istvan
Something in your post messed up the thread so that no Reply button was available so I have removed your post.
You may want to try again, though I did get the gist of what you were getting at.
I had considered the first issue you raised but decided the likelihood of such was so remote as to not bother. The second issue I had not considered but will have a think about that.

 

[Peter_SSs]

Two issues about my suggestion were raised by István Hirsch:

Hi Peter,
I had suggested a similar approach before but now I am trying to find another approach as the very low probability of repeating the same string in the text will stop processing because the first occuration of the repeated string will be always found, for example here:
I am the delegate of the Association: welcome to all the participants of the Conference on Stress Corrosion. Also, welcome to all the participants of the Symposium held at the same time on the same problem here …

Given that this OP's data appears to be addresses, I think the likelihood of of an approximately 40-character string occurring at just the right place to form the contents of one of the result cells and also re-occur at another point in the original text is remote enough to ignore.

More important: as the length of the strings to process is not known before processing, the formula should be copied down according to the longest expected string. If the substring in the last row of a string (which is not the longest expected) is found in the text above the last row, the text following that substring will be processed again

This was a valid point and the example is below, showing how my suggested formulas did not produce the correct results.

Excel Workbook
	A
1	HUNDRED TREES AREA, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015, HONG KONG
2	HUNDRED TREES AREA, 87 WEST COASTAL
3	DRIVE, #12-10, HONG KONG 128015, HONG
4	KONG
5	128015, HONG KONG
6
Split Text (3)

Again, given that we appear to be dealing with address-type data, and breaking it into longish (approx 40 character) chunks, I think the following modification to the A3 (& copied down) formula may suffice.

Excel Workbook
	A
1	HUNDRED TREES AREA, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015, HONG KONG
2	HUNDRED TREES AREA, 87 WEST COASTAL
3	DRIVE, #12-10, HONG KONG 128015, HONG
4	KONG
5
6
Split Text (2)

 

[István Hirsch]

Thanks for correcting my post, Peter. Something went wrong during pasting, and the button of post-editing also disappeared.

I have tried another method. The string to process is in A1, A2 is to be left blank, the formula below should be copied in A3, confirmed with Ctrl + Shift + Enter and copied down:

Excel Workbook
	A	B
1	The awards ceremony was first broadcast to radio in 1930 and televised in 1953. It is now seen live in more than 200 countries and can be streamed live online.[3] The Oscars is the oldest entertainment awards ceremony; its equivalents, the Emmy Awards for television, the Tony Awards for theatre, and the Grammy Awards for music and recording, are modeled after the Academy Awards.	LENGTH
2
3	The awards ceremony was first broadcast	39
4	to radio in 1930 and televised in 1953.	39
5	It is now seen live in more than 200	36
6	countries and can be streamed live	34
7	online.[3] The Oscars is the oldest	35
8	entertainment awards ceremony; its	34
9	equivalents, the Emmy Awards for	32
10	television, the Tony Awards for theatre,	40
11	and the Grammy Awards for music and	35
12	recording, are modeled after the Academy	40
13	Awards.	7
14		0
Sheet

 

[István Hirsch]

Assuming the original text does not include and double, triple etc spaces, then try ..
In A2: =LEFT(A1,40-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,41)," ",REPT(" ",100)),100))))
In A3 and copy down: =IF(A2="","",TRIM(LEFT(MID(A$1,FIND(A2,A$1)+LEN(A2)+1,LEN(A$1)),40-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(MID(A$1,FIND(A2,A$1)+LEN(A2)+1,LEN(A$1)),41)," ",REPT(" ",100)),100))))))

Just a guess (not tested!): the issue of finding the substring (left for the last row) in the body of the text can be eliminated by adding a special character to the end of the text in A1 (for example „A1 & „ß”), then this substring will not be found in the body of the text during processing, resulting the correct working of the formula. Of course, then „ß” should be deleted.

 

[Peter_SSs]

I think the following modifications should allow for all circumstances, including a very common repeated pattern.

Formula in A2 is copied across.
Formula in A3 is copied across and down.

Excel Workbook
	A	B
1	HUNDRED TREES AREA, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015, HONG KONG	abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd
2	HUNDRED TREES AREA, 87 WEST COASTAL	abcd abcd abcd abcd abcd abcd abcd abcd
3	DRIVE, #12-10, HONG KONG 128015, HONG	abcd abcd abcd abcd abcd abcd abcd abcd
4	KONG	abcd abcd abcd abcd abcd abcd abcd abcd
5		abcd abcd abcd abcd abcd
6
Split Text (5)

If we left row 2 blank (& possibly hidden) then this single formula, copied across & down could be used as far as I can see.

Excel Workbook
	A	B
1	HUNDRED TREES AREA, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015, HONG KONG	abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd
3	HUNDRED TREES AREA, 87 WEST COASTAL	abcd abcd abcd abcd abcd abcd abcd abcd
4	DRIVE, #12-10, HONG KONG 128015, HONG	abcd abcd abcd abcd abcd abcd abcd abcd
5	KONG	abcd abcd abcd abcd abcd abcd abcd abcd
6		abcd abcd abcd abcd abcd
7
Split Text (6)

 

[István Hirsch]

The formula in my post #15 needs correcting:

=IF(LEN(A$1)-SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3<41,TRIM(MID(A$1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,LEN(A$1)-SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3)),TRIM(LEFT(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),42-MATCH(TRUE,MID(MID(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),1,42),42-ROW($1:$40),1)=" ",0))))

 

[István Hirsch]

The formula in my post #15 needs correcting:

=IF(LEN(A$1)-SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3<41,TRIM(MID(A$1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,LEN(A$1)-SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3)),TRIM(LEFT(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),42-MATCH(TRUE,MID(MID(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),1,42),42-ROW($1:$40),1)=" ",0))))

It needs correcting again:

=IF(LEN(A$1)-SUM(LEN(A$2:A2))-ROWS(A$1:A3)+3<41,RIGHT(A$1,LEN(A$1)-SUM(LEN(A$2:A2))-ROWS(A$1:A3)+3),TRIM(LEFT(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),42-MATCH(TRUE,MID(MID(REPLACE(A$1,1,SUM(LEN(A$2:A2))+ROWS(A$1:A3)-3,""),1,42),42-ROW($1:$40),1)=" ",0))))

 

[Peter_SSs]

It needs correcting again:

István

- For me it returns #VALUE once it runs out of text to evaluate. eg A6 & below for "HUNDRED TREES AREA, 87 WEST COASTAL DRIVE, #12-10, HONG KONG 128015, HONG KONG"

- Do you see a circumstance where my considerably shorter & non-array-entered formula from the bottom part of post #17 would not work?