Forum Discussion

DanHuber's avatar
DanHuber
Iron Contributor
Dec 29, 2023

PQ: How to split a large column into 50 characters columns but split only at whitespace?

I have a large table (>80'000 rows) with a column (max width = 163) that I need to resize into smaller columns. The rules are: - split only at whitespaces - column size max 50 characters - 2 colu...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 13, 2024

DanHuber 

I haven't followed the details of your requirement but, for interest sake, this is a Lambda function that wraps text to lines breaking at spaces.

"Worksheet formula"
= WrapTextλ(text, 30)

"WrapTextλ"
= LET(
    wordList, TEXTSPLIT(text, , " "),
    wordLen,  LEN(wordList) + 1,
    linePos,  SCAN(0, wordLen, LAMBDA(x, y, IF(x + y < maxLen + 1, x + y, y))),
    lineLen,  FILTER(linePos, VSTACK(DROP(linePos = wordLen, 1), TRUE)),
    lineStrt, SCAN(0, lineLen, LAMBDA(prior, line, prior + line)) + 1 - lineLen,
    MID(text, lineStrt, lineLen)
 )

Resources