Excel Find the Max Value in a column even when?

You'll need to separate the numeric information from the text and place that information into a separate column. If the number is separated from the text by a space then a formula like this will work: =IF(ISNUMBER(A1),A1,VALUE(LEFT(A1,FIND (" ",A1)))) If its not then a beastly array formula like this will separate the numbers out: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID (A1,ROW($1:$9),1)),0),COUNT(1*MID (A1,ROW($1:$9),1))) (you have to press ctrl-shift-enter when entering array formulas) Then you can find the max of the new row of data.

Use a helper column next to your list with this formula: =VALUE(LEFT(A1, FIND(" ", A1, 1))) This will extract the numbers to the left of " Gallons" so long as they all have a space between them Then just use the max function on the helper column =MAX(B1:B600) Change that to fit your exact range. The array formula from Blackened almost works, just make a slight modification: =1*MID(A1, MATCH(TRUE, ISNUMBER(1*MID(A1, ROW($1:9), 1)), 0), COUNT(1*MID(A1, ROW($1:9), 1))) Note the $'s to make ROW 1 absolute. If this isn't done, it fails if you copy it down due to the row #'s changing, and ROW 1 must remain as ROW 1.