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

Separation from two dashes in Excel

Hi Everyone,
Please provide a formulate to separate these in three columns or whatever is the best way to filter it out pls.
Column A2 is filled with ABC-000002
Column A3 is filled with ABC-000002-001
So, basically, I want to filter out these two things, so, i was thinking to use text to column, but that doesn't show initial zeros, then applied this, below formula to divide this into other columns than, I can combine...like that.
this is giving me result as ABD in column B2, And also applied MID function in column C2 but not getting it accurately. So basically I hope you under stood what I am in need of.

Thank you !
if you have a later version of excel like 365


  • Text-Split-ETAF.xlsx
    9.8 KB · Views: 6
This is another example of Microsoft's 'array of array' foul-up!
It is unusual for this type of problem to arise in the context of a single (scalar) text value. It is far more usually applied to a list (maybe a few thousand terms). Yet
= TEXTSPLIT(list, "-")
fails to return the correct result and, instead, truncates the expected array of results to give one column only. Still that's better, I suppose, than more frequently occurring 'array-of-arrays- or 'nested array' errors that Excel returns to justify its refusal to tell you the result of your calculation!

Something that appears to work and return an array of arrays is
= LET(
    initTrim, TEXTBEFORE(list,"-", {1,2,3},, TRUE),
    finalGrp, TEXTAFTER(initTrim, "-",-1,, TRUE),
    IFERROR(finalGrp, "")


  • Text-Split-ETAF=PB.xlsx
    20.3 KB · Views: 4