Share via

SEQUENCE formula does not return dynamic table, why?

Anonymous
2024-05-06T18:19:48+00:00
ABC-XXXX ←INPUT VALUE
FORMULA→ =TEXTSPLIT(A1,"-") =TAKE(TEXTSPLIT(A1,"-"),,1) =LEN(TAKE(TEXTSPLIT(A1,"-"),,1)) =SEQUENCE(LEN(TAKE(TEXTSPLIT(A1,"-"),,1)) =SEQUENCE(E3)
RESULT→ ABC XXXX ABC 3 1 1
2
3
↑ What excel is showing me ↑ What I need
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2024-05-06T18:34:27+00:00

Sequence doesn't like arrays, so change to

=SEQUENCE(MAX(LEN(TAKE(TEXTSPLIT(A1,"-"),,1))))

(TAKE returns an array from TEXTSPLIT's array, so the return from LEN is also an array). MAX collapses the array to a single value (MIN or INDEX or other functions would work as well).

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-05-09T15:57:25+00:00

    Thanks Ashish, sorry, I forgot to mention that my input can have more than one hyphen, my bad. (and not always I'm going to pick the first section)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-05-09T15:55:14+00:00

    Yeah! thank you very much, fantastic!

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 102K Reputation points Volunteer Moderator
    2024-05-06T23:36:46+00:00

    Hi,

    This formula in cell B2 works

    =SEQUENCE(LEN(TEXTBEFORE(A2,"-")))

    Hope this helps.

    Was this answer helpful?

    0 comments