Separation from two dashes in Excel

[Deepak Sharma]

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.
=LEFT(A2,SEARCH("-",A2,1)-1)
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 !

 

[ETAF]

if you have a later version of excel like 365
=TEXTSPLIT(A2,"-")

 

[Deepak Sharma]

Thank you so much buddy, Love you!

 

[ETAF]

you are welcome

 

[Peter Bartholomew]

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