Split a string every X character to the nearest space

[Dexter1759]

Hi,

I've tried to be accurate with my thread title without being too wordy.

But I'm a little stuck for any thoughts/ideas on how to do this in one formula.

Basically I want to be able to type a sentence in cell A1, this sentence could be quite long, so I want to write a formula in cell B1 that will insert a newline every so often, but not in the middle of a word.

My initial logic is simply find the 100th character, find the next space, replace it with a new line, repeat until the end.

I'm aware I'll probably need a fancy array formula, which I'm familiar with to an extent and comfortable with but not when it comes to string manipulation, such as those I've seen on the Chandoo Blog sometimes.

Any help/suggestions would be much appreciated.

Dex

 

[bobhc]

If you are typing in the data why not just use Alt+Return when you want a new line?

 

[Dexter1759]

Sorry, for the purposes of this post I was saying I'd type it in, however it won't be.

 

[Mike H..]

Hi,

I've tried to be accurate with my thread title without being too wordy.

But I'm a little stuck for any thoughts/ideas on how to do this in one formula.

Basically I want to be able to type a sentence in cell A1, this sentence could be quite long, so I want to write a formula in cell B1 that will insert a newline every so often, but not in the middle of a word.

My initial logic is simply find the 100th character, find the next space, replace it with a new line, repeat until the end.

I'm aware I'll probably need a fancy array formula, which I'm familiar with to an extent and comfortable with but not when it comes to string manipulation, such as those I've seen on the Chandoo Blog sometimes.

Any help/suggestions would be much appreciated.

Dex

Hi,

This is a function I got from an old posting friend called Rick Rothstein over on Microsoft Answers.

It does what you want but there's a couple of caveats. Because a function (Native or user-defined) can only return a value and cannot alter the formatting of a cell you must do this manually.

1. Format the cell for line wrap.
2. manually make the cell wide enough an deep enough to display the wrapped text.

We could get around 1 & 2 by doing this with a macro instead of a function but IMHO having to manually format the cell(s) is trivial.

On the worksheet call with:-

=WrapText(A1,100)

You can change 100 to whatever value you want.

Function WrapText(CellWithText As String, MaxChars) As String
  Dim Space As Long, Text As String, TextMax As String
  Text = CellWithText
  Do While Len(Text) > MaxChars
  TextMax = Left(Text, MaxChars + 1)
  If Right(TextMax, 1) = " " Then
  WrapText = WrapText & RTrim(TextMax) & vbLf
  Text = Mid(Text, MaxChars + 2)
  Else
  Space = InStrRev(TextMax, " ")
  If Space = 0 Then
  WrapText = WrapText & Left(Text, MaxChars) & vbLf
  Text = Mid(Text, MaxChars + 1)
  Else
  WrapText = WrapText & Left(TextMax, Space - 1) & vbLf
  Text = Mid(Text, Space + 1)
  End If
  End If
  Loop
  WrapText = WrapText & Text
End Function

 

[Dexter1759]

Hi,

Thanks for the above, I was trying to avoid using VBA if possible, I prefer to use formulae. Purely from a self learning stand point, I can write VBA very comfortably, but currently prefer to increase my formula knowledge.

I was inspired by some the of formulae that were submitted as part of this challenge.

http://chandoo.org/wp/2013/07/22/fo...ring-after-the-first-block-of-numbers-part-4/

And was hoping to be able to put together a single formula that could figure out the break points, and then use the replace function to insert a new line at those points. Alas, I seem to have hit a bit of a sticking point in trying to do this and I'm not sure it's possible. I've built up a table of formulae that result in a column of TRUE/FALSE values, where TRUE is the break point locations. But I'm struggling utilise the REPLACE function with multiple insertion points.

Is this possible?

Dex