Comparing app version numbers

[saimike]

I maintain a excel table of app versions that were tested before. Before testing a new version, I would compare it to the previous version. However, version numbers aren't always "legitimate math numbers". A simple if(X>Y, do this, do that) doesn't always work due to this.

For example, 3.2.12.4 or 9.22.CA-1. When we eyeball it, we naturally know that 9.22.CA-1 is bigger than 3.2.12.4 because 9 > 3. What's the best way to compare these "numbers"? Do I need a function or is there a formula I can use? I'd like to avoid array formulas where possible.

[martindwilson]

works for that example,need a bit more data to look at