1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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

    Hui...

  3. 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

Discussion in 'Excel Challenges' started by Dave2018, Dec 27, 2018.

  1. Dave2018

    Dave2018 New Member

    Messages:
    5
    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:

    [​IMG]

    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!)
  2. vletm

    vletm Excel Ninja

    Messages:
    4,645
    Dave2018
    Your A1 seems to be D-something
    as well as A2 D-something.. Screen Shot 2018-12-29 at 20.48.18.png
  3. Dave2018

    Dave2018 New Member

    Messages:
    5
    sorry, but that is creating a text string.

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

    vletm Excel Ninja

    Messages:
    4,645
    Dave2018
    You wrote something and
    now You wrote something else ...
    as well as Your sample picture
    ... sorry.
  5. AliGW

    AliGW Active Member

    Messages:
    318
    How about the Text to Columns feature on the Data ribbon using the pipe symbol (|) as the delimiter?
  6. Fluff13

    Fluff13 New Member

    Messages:
    1
    FilterXml & substitute? ;)
    Khalid NGO likes this.
  7. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,976
    Hi,

    Got the idea :awesome:


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

    Never used FILTERXML, not even saw this function before :)

    Regards,
    Chihiro and Thomas Kuriakose like this.
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
  9. Dave2018

    Dave2018 New Member

    Messages:
    5
    Fluff13 - correct!

    khalid, also correct.

Share This Page