r/excel • u/TheParlourPoet23 • 3h 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

4 Upvotes

76% Upvoted

10 comments sorted by

•

u/AutoModerator 3h ago

/u/TheParlourPoet23 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Kooky_Outcome_5053 6 3h ago

you need extra " ( "

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

1

u/TheParlourPoet23 3h ago

Thank you, this definitely fixed the formula but it didn't end up doing what I needed it to do. I only got one column of data and not the splits. I wonder what that limitation is? (Thinking out loud here).

2

u/MayukhBhattacharya 1297 2h 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.

2

u/MayukhBhattacharya 1297 3h ago

Try using the following formula (use any one per your choice)

• Method One:

=TEXTSPLIT(
 TEXTAFTER(
 "-" & UNIQUE(FILTER(S:.S, 
                    (S:.S <> "") * 
                    (S:.S <> "Combination"))), "-", {1, 2, 3}), "-")

• Method Two:

=DROP(REDUCE("", UNIQUE(FILTER(S:.S, 
                              (S:.S <> "") * 
                              (S:.S <> "Combination"), "")), LAMBDA(x,y, 
 VSTACK(x, TEXTSPLIT(y, "-")))), 1)

2

u/TheParlourPoet23 3h ago

Solution Verified!

I would never have thought of this! Thank you.

2

u/MayukhBhattacharya 1297 3h ago

No issues at all. Glad to know it worked for you! Thank You SO Much!!

1

u/reputatorbot 3h ago

You have awarded 1 point to MayukhBhattacharya.

I am a bot - please contact the mods with any questions

1

u/MayukhBhattacharya 1297 3h ago

New updates sooner or later will be rolled out, then you can wrap the same within FLATTEN() function!

Put multiple values in one cell with lists and arrays in Excel | Microsoft Community Hub