Find closest space to the left of specified position and then split

Hi!

I'm looking for a way to split a cell with text into two cells. The first cell can only contain up to 40 characters, the rest needs to go in the second cell. However, I don't want the text to be split in the middle of a word. So basically, if the text is in cell A1, I need to find the closest space to the left of position 40 in A1, and move anything to the right of this position to B1. Example:

Original text:
A1: One green apple and a bucket of small onions that smell nice

I want to avoid this:
A1: One green apple and a bucket of small on
B1: ions that smell nice

I want to achieve this:
A1: One green apple and a bucket of small
B1: onions that smell nice


Any ideas?

Can do this with a pair of user defined functions

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In B2, enter =FirstBit(A1)
In C2, enter =SecondBit(A1)

Remember to save the workbook as a macro enabled workbook .xlsm

Originally Posted by mrice

Can do this with a pair of user defined functions

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In B2, enter =FirstBit(A1)
In C2, enter =SecondBit(A1)

Remember to save the workbook as a macro enabled workbook .xlsm

Fantastic, thank you very much! :D