Here I am sharing you an awesome Google Sheets Trick. Do you know you can wrap text using formula in Google Sheets? If not I’m revealing that trick. But before that let me clarify one thing. It’s not a perfect word wrap. It has limitation but you can overcome it a little bit. Because, with this wrap, you can wrap only single word per line. No control over column width. You can use any length sentence for word wrap using my short formula.
Wrap Text Using Formula in Google Sheets
Here I’m using a combination of Google Sheets Text formulas to word wrap in a cell. What are those nested formulas then?
SPLIT, CHAR, and JOINÂ are those tricky formulas. I’m going directly to the word wrap formula. Head to our Google Sheets Function Guide to learn the above-said functions in details and also with advanced tips.
The Formula to Wrap Text or Sentences in Google Sheets
When your text or sentence is in Cell A2, you can use the below formula in cell B2 to get the wrapped text.
=join(char(10),split(A2," "))
See the screenshot again.
The Limitation:
The above short formula puts each word in a new line. It has not controlled over the column width. So to overcome this limitation there are limited options. See this modified formula.
Here there is a long sentence in cell A3 though part of the text is hidden. You can see the wrapped form of this sentence in Cell B3.
What is the difference with this formula compared to the first one? Here I’ve used Comma delimiter in the SPLIT function whereas in the first formula I’ve used the SPACE delimiter. So accordingly the wrapping happened.
When you want to have more control over the Word Wrap using formula in Google Sheets, just manually add Comma to the sentence in Cell A3, as per the above example, and use the second formula. But I think it’s not legible as putting additional commas to your original sentence could spoil it.
In such cases, you should think about the Regexreplace function. Here are some more examples to Wrap Text Using Formula in Google Sheets.
The Regex Formula to Wrap Two Words Per Line
Text in Cell A1: “This is a google sheets tutorial”
Formula:
=trim(regexreplace(A1, "(\w+\s*\w+)", char(10)&"$1"))
Result:
“This is
a google
sheets tutorial”
The Regex Formula to Wrap Three Words Per Line (sometimes random)
Formula:
=trim(regexreplace(A1&" ", "(\w+\s\w+\s\w+)", char(10)&"$1"))
Though this formula is set to wrap the sentence after every third word, it behaves differently.
The comma and full stop, if any, between affect the wrap and give you a natural word wrap feel. So I would like to call it a random wrap formula.
Hope you could understand the above tutorial on wrap text using formula in Google Sheets. I hope it may be useful for some of you.
