How do I split Data based on 2 Dashes?

[CharlesWilliams]

Here’s an example of what I mean:

It could be in one of the following ways: in Column A…

If it looks like:
Acme-Market-12345

I would like it to look like:
Column A
Acme Market
Column B
12345

If it looks like:
Acme-Market-123-45

I would like it to look like:
Column A
Acme Market
Column B
123-45

If you need more clarification please let me know.

Update: 2019-06-20T18:31+02:00

The records look good at the top but as you go down further they get messed up

Maybe I should clarify my data and provide a little sample data (Like I should have the first time). My data has the brand name at the beginning (Always letters) and then the part name.

Attached is a sample of the records that get messed up.Sample Data.ods

[CharlesWilliams]

OK, so I thought it worked.

BTW: Wow! I’ve been working with Excel since the Mid 80’s and never used this command. You gave me something to check into further.

[erAck]

Excel doesn’t know regular expressions, neither in the Find&Replace dialog nor the REGEX() function, so it’s no wonder you didn’t come accoss.

Apart from that, your “answer” is not an answer to the original question. Please either use add a comment for comments, or to add details to your question use the edit button on the question. Thanks.

[erAck]

I just moved the update into the original question.

[erAck]

Please note the update in my answer. And thanks for moving your answer to a comment.

[erAck]

Copy data from column A to column B.

Then select data in column A and with Find & Replace (Ctrl+H)

• Find: ^([^-]+)-([^-]+)-(.+)
• Replace: $1 $2
• Other options:
• Current selection only
• Regular expressions

Hit Replace All.

Then select data in column B, apply the Text format (from Ctrl+1 Numbers), and then with Find & Replace (Ctrl+H)

• Find: ^([^-]+)-([^-]+)-(.+) (same expression as before)
• Replace: $3
• Other options:
• Current selection only
• Regular expressions

Hit Replace All.

The Text format is necessary because otherwise data like 12345 would become numeric data and may lose digits if more than accepted precision or leading zeros are involved. Maybe the same for column A if there may be digits-only company names…

Update: 2019-06-20T18:38+02:00

The original question was how to split data based on 2 dashes, but your data may contain only one dash as well. You didn’t say that. So apparently you want to split on one dash, which separates non-numeric from numeric data, and replace all earlier dashes with blanks. But even not only that, if you want Acme-Market-12345 become Acme Market and 12345, and at the same time let appleton-aaua-15 become appleton and aaua-15. Good luck with that. On the other hand, your sample sheet contains only data that is to be split on the first dash and keep intact all following dashes. Please clarify.

[anon73440385]

If OP prefers use of formulas the following might also work (assumimg cell C1 contains the data to be split)

• Column A: =REGEX(C1,"^([^-]+)-([^-]+)-(.+)","$1 $2")
• Column B: =REGEX(C1,"^([^-]+)-([^-]+)-(.+)","$3")

[CharlesWilliams]

I guess I’m really trying to put a square peg into a round hole.

The records are mixed in a few ways:
Acme-12345
Acme-Market-12345
Acme-Market-123-45
Acme-Market-12-3-45

I might have to fuss with the formulas given for each particular instance of bad records.

[Lupp]

As always with questions of the kind any solution needs to be based an a syntactical analysis whether simple or complicated.
-1- You can describe the syntax with ordinary words if simple enough - or if very tricky in some details.
-2- In many cases you can describe it with the help of Regular Expressions - if you are familiar with them.

-3 (Problematc!)- If you don’t find your way either with the first nor with the second suggestion, you can try to make it clear based on examples. This third way is problematic, however, because you leave much to guessing on behalf of the helper. The examples need to be really complete and significant.

Therefore such questions often result in a waste of time.

You did neither describe the task clearly in words nor by formal means, and your solved examples look as if they cannot be seen based on a clear specification at all. You will need to disprove my claim before help can go on.

===Edit 2019-06-20 18:30 UTC===
A bit of further investigation showed undisputable contradictions between the expected solutions listed in the attachment and the examples given in the original question.