I have a list of OSs running on a bunch of servers in my workplace. The string giving the OS can be of many formats. Here are some samples:

* AIX 5.1
* VMware ESX Server 3.5.0 build-110268
* Linux Linux 2.6.X Linux
* Red Hat Enterprise AS 4 (2.6.9-78.0.13.ELlargesmp)
* Microsoft (R) Windows (R) 2000 Advanced Server
* Microsoft Windows XP Win2008R2 6.1.7601

There are 180 unique strings in all (and >10,000 servers).

My boss is asking for a formula that will extract the OS type and version from each string. I have made one for the OS type that works reliably by looking up a table with ten entries. But the version formula is tougher, as the format of the version is dependent on the type of OS, and even then there can be multiple formats per OS type.

As far as I'm concerned you would need a table containing 180 rows, with the strings in COL A, the versions in COL B, and then do a Vlookup on the string. However, he is demanding the version be extracted from the string, using only excel formulas, no vba.

I've pretty much given up at this point, so I'm throwing it out to this website. Anyone know if this can be done, and how?