Solved

Break a string of text (sentence or paragraph) at a space just < 50 in Excel

Posted on 2014-12-30
10
109 Views
Last Modified: 2015-01-13
Hello,

Can someone suggest a formula in Excel (2013) that will break a long string of text (like a sentence or paragraph) located in cell A1 into blocks (in column B) which:

        a) each have a length of < 50 characters and
        b) each are broken at the last space before 50 in order to avoid divided words?


For example, suppose the entry in cell A1 is as follows:
I receive lots of great help and solutions at Experts Exchange. It is a great resource and well worth the monthly fee.
This entry contains 118 characters and if broken into exactly 50-character segments in column B,  would appear as follows (with the number of characters shown in parentheses to the left):

(50)        B1 = I receive lots of great help and solutions at Expe
(50)        B2 = rts Exchange. It is a great resource and well wort
(18)        B3 = h the monthly fee.

I'm looking for a formula to paste in column B which, instead of inserting the break at exactly 50 characters, will identify and create the breaks at the last space just shy of 50:

(46)        B1 = I receive lots of great help and solutions at
(50)        B2 = Experts Exchange. It is a great resource and well
(22)        B3 = worth the monthly fee.

Note that when the breaks are made, the space at those locations is not lost. In the above example, a space is present after the words "at" and "well" in the first two segments respectively.

Also, it's clear that the formula will have to take into account the number of characters which end up up in each of the preceding cells.

Thanks
0
Comment
Question by:WeThotUWasAToad
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525089
What happens to the string of text entered in A2?
What is wrong with just word wrap in the cell and a higher Row height?
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40525116
Hello,

such complex evaluation is not easily done with a formula. This would require VBA, because there is a certain element of looping involved that a formula cannot provide.

I'm not even sure it can even be done with VBA, but I'm pretty sure that it's not possible with formulas.

cheers, teylyn

PS. Happy New Year, Steve
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 100 total points
ID: 40525140
Hi,

pls try

=IF(IFERROR(LEN($A$1)>SUM(LEN(B$1:B1)),TRUE),IF(LEN($A$1)-IFERROR(SUM(LEN(B$1:B1)),0)>49,
MID($A$1,IFERROR(SUM(LEN(B$1:B1)),0)+1,LOOKUP(2^15,FIND(" ",MID($A$1,IFERROR(SUM(LEN(B$1:B1)),0)+1,50),ROW(INDIRECT("1:"&LEN(LEFT($A$1,50))))))),RIGHT($A$1,LEN($A$1)-IFERROR(SUM(LEN(B$1:B1)),0))),"")


as an array formula (Ctrl-Shift-Enter) in B2 then fill up b1 and fill down B6 or more

see example

Regards
EE20141231.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40525167
I need to revise my definition of "not possible".
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40525227
@teylin thank you for the compliment and "Guten Rutsch ins neue Jahr"
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525299
BUT...

Like I said, what happens now when you have an entry in A1 And A2 And A3 And..... A24 ?
At what point does that become dynamic?  I assume that the questioner does not have a spreadsheet with JUST 1 row of data in it?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 400 total points
ID: 40526165
Hello Steve,

I used this formula in B1

=IF(A1="","",LEFT(A1,LOOKUP(50,FIND(" ",A1&" ",ROW(INDIRECT("1:"&LEN(A1)))))))

then this one in C1 copied across as far as you need

=IF($A1="","",REPLACE(LEFT($A1,LOOKUP(50+SUMPRODUCT(LEN($B1:B1)),FIND(" ",$A1&" ",ROW(INDIRECT("1:"&LEN($A1)))))),1,SUMPRODUCT(LEN($B1:B1)),""))

Neither formula is an array formula and both should work in any version of excel from 2003

See attached example

Happy New Year!

regards, barry

PS to address neilsr's point. If you make the data in a table in Excel 2007 or later then you can add rows as you wish and the formulas will automatically populate the new rows (but obviously you need to include the formulas in enough columns to accommodate the maximum length of text).
split-text.xlsx
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40545860
Thanks for the responses.

Barry, your solutions are always so…elegant.

Is that an adjective that can be applied to an Excel formula? I know it works for solutions and formulas in other disciplines so I assume it can be used here as well. :)
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40547551
Thanks Steve,

Elegant works for me, I have certainly seen that adjective applied to excel formulas before.

Elegance and simplicity are two of the attributes I'm striving for - my New Year resolution is to add transparency, if possible, difficult with the complex formulas here........:)

regards, barry
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40548250
Ohhh, I could use some transparency, "Barry". When it comes to your formulas, I often feel like Sisyphus

Steve, whenever "Barry" posts in a question I bow out. He always has the trump card, the perfect solution, the most elegant way. He's in a different universe. His blood is probably green.

Happy New year to you both,

cheers, teylyn
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question