Looking for code to separate one line of text into multiple lines in Excel

[petermc11]

I often need to manipulate a string of data, sometimes many hundreds
of records, (up to 125 characters) into multiple columns - usually
five - for import into an application program. I've used split cell
into columns function of Excel 2003 but it still requires some manual
and semi-automated concatenation of resulting cells (up to 100+ per
record!).

I have an upper limit of 25 characters per new cell. I'd like to
split text into the multiple columns without cutting off a word in the
middle. e.g.:

Original text:
A long string that needs to be split into 5 adjacent cells of up to 25
characters and not splitting individual words

Ideally, resulting text should look something like this (each line
shown in separate row but actual result will be in same row, adhacent
columns):
A long string that needs
to be split into 5
adjacent cells of up to
25 characters and not
splitting individual word

Note that last character in last line ("s" in"words") was truncated
because it eceeded 25 characters.

Any help would be appreciated.

 

[Ron Rosenfeld]

I often need to manipulate a string of data, sometimes many hundreds
of records, (up to 125 characters) into multiple columns - usually
five - for import into an application program. I've used split cell
into columns function of Excel 2003 but it still requires some manual
and semi-automated concatenation of resulting cells (up to 100+ per
record!).

I have an upper limit of 25 characters per new cell. I'd like to
split text into the multiple columns without cutting off a word in the
middle. e.g.:

Original text:
A long string that needs to be split into 5 adjacent cells of up to 25
characters and not splitting individual words

Ideally, resulting text should look something like this (each line
shown in separate row but actual result will be in same row, adhacent
columns):
A long string that needs
to be split into 5
adjacent cells of up to
25 characters and not
splitting individual word

Note that last character in last line ("s" in"words") was truncated
because it eceeded 25 characters.

Any help would be appreciated.

You can do this using Regular Expressions, although the last one is tricky,
since you want to truncate at 25 characters rather than at a word break.

The easiest way to implement this, since your strings are less than 256
characters, would be to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/ The add-in is easily distributable with the
workbook if that is an issue.

Then use these formulas (assuming your string is in A1:

B1: =TRIM(REGEX.MID($A1,".{1,25}\b",COLUMNS($A:A)))
Copy/drag across to E1.

F1: =LEFT(TRIM(MCONCAT(REGEX.MID($A1,".{1,25}\b",{5,6}))),25)



--ron