wrapping long lines *within* a Sheets cell

user_10761353 0
Viewed 44

So I've got some long lines in a cell, and I'd like to force-wrap the lines, before the last space before the 180th character.

i.e. of the string [170-190] is:

test foo bar baz band

Since the 180th character is the a in bar, I'd like it to become

test foo\n bar baz band

Similar to the requirement in this question, but using Sheets' built-in functions rather than Python/Ruby.

Dead-ends so far

String methods

Searching online, I came across this post, and derived this to find the last space before 180, and keep only what's to the left():

=left(B5,FIND("",SUBSTITUTE(LEFT(B5,180)," ","",LEN(LEFT(B5,180))-LEN(SUBSTITUTE(LEFT(B5,180)," ",""))),1)-1)

I imagine to get this to work multiple times per line would require an exponential complexity explosion or a custom function.

REGEXREPLACE

I'm looking into regexreplace, since I'd like this replacement to occur as many times as needed (a single line could need to wrap 2+ times).

Playing with regex101.com, I can find the 180th non-newline with ^\n{180}, but I've not been able to find anything that explains how one might capture the line's characters only up to the preceding space.

google-sheets regex
1 Answers

Try this:

=REGEXREPLACE(B5,"(.{180}) ",CONCAT("$1",char(10)))

It counts 180 characters (captured with group 1) followed by a space and it replace it with the content of group 1 concatenated with a newline (char(10)).

EDIT: If you need to match strictly less than 180 characters, then you can use:

=REGEXREPLACE(B5,"(\b[^\n]{0,180}) ",CONCAT("$1",char(10)))

EDIT: tightening the range to not add newlines after short lines

=REGEXREPLACE(B5,"(\b[^\n]{150,177}) ",CONCAT("$1",char(10)))
user_46077330
Related