The VALUE() function should have turned any non alphanumeric into a number, otherwise an error (which was fine for my formulas purposes). Try changing the format of the column to number or general.

As for the machine numbers, you could use the formula =RIGHT(D2,3) in column N and adjust my COUNTIFS() with the additional criteria when you have ranges like SH420-075:SH450-320.