• 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!)


New Member
sorry, but that is creating a text string.

i am looking to parse my string into an array of separate values


Active Member
How about the Text to Columns feature on the Data ribbon using the pipe symbol (|) as the delimiter?