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

 

[Peter_SSs]

Can you confirm whether you need whole "words" kept together?
Or would you be happy with the first cell for that sample data to be the following? "HUNDRED TREES, 87 WEST COASTAL DRIVE, #1"

Edit: What is the maximum length (approximately) that the original text is likely to be?

 

[SUNNY ISLAND]

Whole Words should be kept together

Therefore, the first cell should be:

HUNDRED TREES, 87 WEST COASTAL DRIVE,

Second cell should read
#12-10, HONG KONG 128015

 

[Peter_SSs]

.. and did you see the edit in my last post?

 

[SUNNY ISLAND]

The original text length vary. Have a few hundred records which we need to split into cells with text length capped at 40 each.

 

[Peter_SSs]

The original text length vary.

Yes, but might we need to split it into 100 different cells, or might, say, 3 (or some other number) cells be sufficient?

 

[SUNNY ISLAND]

the max of cells allowed is 5

 

[Peter_SSs]

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

 

[SUNNY ISLAND]

Thank you. It works.

As there are cells with text length more than 100, can I trouble you to assist with the formula in cell A4 and A5 as well, as each cell can consist 40 characters only.

 

[Peter_SSs]

.. can I trouble you to assist with the formula in cell A4 and A5 as well,

I already did.

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