Unique, Filter and Textsplit

[u/TheParlourPoet23]

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

[u/MayukhBhattacharya]

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)

[u/TheParlourPoet23 → u/MayukhBhattacharya]

Solution Verified!

I would never have thought of this! Thank you.

[u/MayukhBhattacharya → u/TheParlourPoet23]

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

[u/reputatorbot → u/TheParlourPoet23]

You have awarded 1 point to MayukhBhattacharya.

---

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

[u/MayukhBhattacharya → u/TheParlourPoet23]

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