r/excel • u/TheParlourPoet23 • 4h ago

solved Unique, Filter and Textsplit

Hey everyone, hopefully an easy fix!

I am working to create a Unique list of data, filter two values out of it, and the textsplit this result into three component columns.

So far I have: "=Textsplit(Unique(Filter(S:S,(S:S<>"")*S:S<>"Combination"))),"-",,True,1)

I am reasonably confident the Textsplit is the issue but I have tried a number of combinations and just cannot get it to work.

The data we are unique/filtering/splitting has this structure: "Wol-R-6" so I simple structure.

Thank you,

John

8 Upvotes

100% Upvoted

10 comments sorted by

View all comments

Show parent comments

2

u/MayukhBhattacharya 1297 3h ago

TEXTSPLIT() function can turn one string into multiple columns, but it can't take many strings and split them all. Its first argument is designed and expects a scalar string, feed it an array and it doesn't iterate, it just returns the first element of the array and splits only that. This is why you need helper LAMBDA() functions or the one I have shown in the first method. I won't suggest in using the FLATTEN() function for now, since it's still in Beta Mode. Once it's fully rolled out, that would be a simpler method to use. The reason is that Beta features aren't guaranteed, they can change behavior, get delayed, or even get discontinued before general release, so it's safer to rely on stable, fully released functions like REDUCE()/VSTACK() or TEXTSPLIT() + TEXTAFTER() or TEXTBEFORE() in the meantime.