0

I have table with strings like this:

abc-1.2.3-1
abc-1.2.11-3
abc-1.11.3-2
abc-1.2.11-21
abcd-12345-7
abcd-12345-21
abc-def-1-3

Now, what I know for sure that there is a name (I don't know the length, it can be 1 character as well as it can be 15 characters and it can contain hyphens itself) then hyphen character and then version number.

As you have probably noticed, "version numbers" have different structures, one looks like 12345-7 and in next line there can be 1.2.3-3 (basically, I know this is a number with optional dots beetwen digits (1.2.3 part) which ends with hyphen and a number (e.g. -23).

If I simply sort it, then I have something like this:

abc-1.11.3-2
abc-1.2.11-21
abc-1.2.11-3
abc-1.2.3-1
abcd-12345-21
abcd-12345-7
abc-def-1-3

What I'd like would be:

abc-1.2.3-1
abc-1.2.11-3
abc-1.2.11-21
abc-1.11.3-2
abcd-12345-7
abcd-12345-21
abc-def-1-3

Sort it alphabetically, but whenever you find a number, treat it like a number.

I've seen a solutions with using functions LEN/MID/RIGHT and similar, but don't know how to apply them to my case.

kfasny
  • 235
  • 1
  • 3
  • 8
  • Is there always a dash before the first number? If so you may be able to do this fairly simply? – Trum Aug 28 '15 at 09:28
  • Yes, there is always a dash. – kfasny Aug 28 '15 at 09:36
  • Unfortunately, the last example 'abc-def-1-3' scuppers things being simple in this way, as the dash falls in the text string. Is this likely to be a one off, or are there more in this way? If the latter, you might have to consider something more drastic to get the correct strings out - like a VBA regex function – Trum Aug 28 '15 at 09:43
  • There's more, I have just found rows like abc-0.1.1-asd-fda-0.3.3.3.3-1.3.2 If it would be just for me I'd use programming language to take care of parsing that, but it has to be simple for random user, so he can just copy and past :/ and then it has to be in Excel so it can be also modified by those random users – kfasny Aug 28 '15 at 09:54
  • 1
    Maybe have a look at - http://stackoverflow.com/questions/4809453/how-to-match-a-simple-number-pattern-in-vba-using-regex You should be able to pull numbers out this way, and could substitute or use a separate function to get only the text strings. If you can get the two strings out, you can fairly easily sort them that way. Unfortunately, because of the moving layout, I think this is your only consistant way of doing things. – Trum Aug 28 '15 at 09:59
  • Yeah, I may end up doing this, but the question raises, is it simple enough for a random user to modify it? Anyway, thank you for your help :P – kfasny Aug 28 '15 at 10:05

0 Answers0