Parsing text with different number of dashes

[Avit]

I have a list of name conventions
Some have three dashes, e.g. AA-BB-CCC
Some have 4, e.g. AAA-BB-CCC-DD.

Assume also the length of each word can be anywhere from 2 to 4 letters.
e.g.
AAA-BB-CC
or
AA-BBB-CCC
etc.


I want to move separate them all into columns (3 to 4, as needed). or in other words, I need to find a way for Excel to extract all text between 2 dashes, for the first appearance, the second, the third and when applicable the forth.

How do I do that?

Thanks a lot!!

 

[Fuji]

TextToColumns?

Split text into different columns with the Convert Text to Columns Wizard - Microsoft Support

Learn how to take the text in one or more cells, and split it out across multiple cells by using Excel functions. This is called parsing, and is the opposite of concatenate. Watch more in this video.

support.microsoft.com

 

[Avit]

Thanks all, found a solution (yes, with AI, not sure if I am allowed to post it, so I am not, but it's working). Thanks!!

 

[Alex Blakenburg]

Thanks all, found a solution (yes, with AI, not sure if I am allowed to post it, so I am not, but it's working). Thanks!!

My understanding is that it is perfectly ok for you to post what worked for you and mark is as the solution, so that it may help others with a similar issue.
Just don't do a straight cut and paste of the whole spiel that the AI normally comes out with.

 

[Avit]

Oh, in that case:
Column 1:
=LEFT(A1, FIND("-", A1) - 1)

Column 2:
=MID(A1, FIND("-", A1) + 1, FIND("-", A1, FIND("-", A1) + 1) - FIND("-", A1) - 1)

Column 3:
=IFERROR(MID(A1, FIND("-", A1, FIND("-", A1) + 1) + 1, IFERROR(FIND("-", A1, FIND("-", A1, FIND("-", A1) + 1) + 1) - FIND("-", A1, FIND("-", A1) + 1) - 1, LEN(A1))), "")

Column 4:
=IFERROR(MID(A1, FIND("-", A1, FIND("-", A1, FIND("-", A1) + 1) + 1) + 1, LEN(A1) - FIND("-", A1, FIND("-", A1, FIND("-", A1) + 1) + 1)), "")

 

[Scott Huish]

You didn't say what version of Excel you have. If you had a modern version you could just use Textsplit

 

[alansidman]

Additionally, you could use Power Query to split the data to multiple cells.