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 colums with with 50, third column with rest (charpos > 100). I can use PQ to split at whitespaces OR at 50 chars.But how can I split only at whitespaces while trying to make the first two columns 50 chars wide, if possible?

To resize a large table column in Microsoft Excel: Use the Text to Columns feature. Choose "Delimited" and select "Whitespace" as the delimiter. Set column widths to a maximum of 50 characters. For the third column, specify a width greater than 100 characters. This will efficiently split the specified column based on whitespace and meet the defined size criteria. [link removed by admin]

DanHuber Could you see how it goes with the attached PQ option?

Thanks, but I am looking into a PowerQuery (PQ) solution . I appologize for not writing this the right way...

Thanks Lorenzo I have to admit, I am very hesitant to open an unknown excel file with external data connections on my working environment. I would like, though, since your solution interests me a lot. Is there any way you could share differently? Perhaps with the M code directly? I'm just very very careful. Or try to be 🙂 Daniel

DanHuber I share your caution and you're absolutely right. Take a look at my profile, nobody complained so far...If after that you still hesitate let me know and I'll see what I can do

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

11 Replies

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)
 )

Lorenzo
Silver Contributor
Dec 29, 2023
DanHuber

Could you see how it goes with the attached PQ option?
PQ_Split_DanHuber.xlsx20 KB
- DanHuber
  Iron Contributor
  Dec 29, 2023
  Thanks Lorenzo
  I have to admit, I am very hesitant to open an unknown excel file with external data connections on my working environment. I would like, though, since your solution interests me a lot.
  
  Is there any way you could share differently? Perhaps with the M code directly?
  
  I'm just very very careful. Or try to be 🙂
  
  Daniel
  - Lorenzo
    Silver Contributor
    Dec 29, 2023
    DanHuber
    
    I share your caution and you're absolutely right. Take a look at my profile, nobody complained so far...
    If after that you still hesitate let me know and I'll see what I can do
henryjones712
Copper Contributor
Dec 29, 2023
To resize a large table column in Microsoft Excel: Use the Text to Columns feature. Choose "Delimited" and select "Whitespace" as the delimiter. Set column widths to a maximum of 50 characters. For the third column, specify a width greater than 100 characters. This will efficiently split the specified column based on whitespace and meet the defined size criteria. [link removed by admin]
- DanHuber
  Iron Contributor
  Dec 29, 2023
  Thanks, but I am looking into a PowerQuery (PQ) solution . I appologize for not writing this the right way...

Forum Discussion

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

11 Replies

Resources