parse cell without array formula

[Dave2018]

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

 

[vletm]

Dave2018
Your A1 seems to be D-something
as well as A2 D-something..

 

[Dave2018]

sorry, but that is creating a text string.

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

 

[vletm]

Dave2018
You wrote something and
now You wrote something else ...
as well as Your sample picture
... sorry.

 

[AliGW]

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

 

[Fluff13]

FilterXml & substitute?

 

[Khalid NGO]

Hi,

Got the idea

FilterXml & substitute?

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"|","</b><b>")&"</b></a>","//b")

Never used FILTERXML, not even saw this function before

Regards,

 

[Chihiro]

FilterXML is very useful in retrieving specific value from WebService function's xml reponse. Say, checking API response after sending query URL.

Below is pretty good tutorial on the function.
https://analystcave.com/excel-webservice-filterxml-functions/

 

[Dave2018]

Fluff13 - correct!

khalid, also correct.

 

[Peter Bartholomew]

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

 

[AliGW]

A bit shorter:

=LET(t,TEXTSPLIT(A1,,"|"),IFERROR(--t,t))

where A1 contains the string.

 

[Peter Bartholomew]

@AliGW
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?

 

[AliGW]

It’s modern foreign languages.