Rank cells with commas

[thewrathful]

I have a list of numbers that looks like:

1,2,3,2,0
3,1,2,4,1
4,5,6,7,8

etc

and i've tried using the SMALL formula to rank them but it keeps coming up with an error message like #VALUE. Is there a modification i can make to the formula that will allow me to rank them even with commas in?


OR



is there a formula that will REMOVE commas?



Thanks a bunch guys.
W

[JBeaucaire]

If those values were in column A, then in B1 enter this:

=SUBSTITUTE(A1, ",", "") + 0

Copy that down. Now run a RANK() on the new values in column B.

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].