• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

parse cell without array formula


New Member
type in A1: "1|A|2|B|33|CC|4D|5CCC|1F|6YY43F5|9CD" without the quotes.

Challenge: In A2, type a formula that will parse the string into {1;"A";2;"B";33;"CC";"4D";"5CCC";"1F";"6YY43F5";"9CD"} when you evaluate it in the formula bar with F9, as in the screenshot:

The Catch: there are some rules about the formula:

1)No UDF, only native functions
2)Cant use MID

(my solution uses 2 functions, and each of them are only used once!)
sorry, but that is creating a text string.

i am looking to parse my string into an array of separate values
How about the Text to Columns feature on the Data ribbon using the pipe symbol (|) as the delimiter?
All comes to he who waits!
= TEXTSPLIT(target,,"|")
Doesn't return numbers, though. For that one might need
= LET(
    text, TEXTSPLIT(target,,"|"),
    IFERROR(VALUE(text), text)
I think that is about as concise as it gets! I find these challenges to be a good source of problems that allow me to test 365. Not that I always follow the rules of the challenge. For me, exploiting array methods is a primary goal. My other aim is to make the formulae as readable as I can; after all, one day I may return to one and need to understand what it does.
BTW, what is MFL?