@ > Home > Contents > Convert String to Number Excel 97+

Problem

Many functions like MAX, MIN, SMALL, LARGE and RANK require numbers though also string analysis could be answered by them. Sometimes array formulas even depend on them, so answers with strings are not possible using such a direct way. And sorting a range for answers should not be an exclusive way.

Solution

Converting strings to numbers requires practical assumptions:

- Strings are sorted by comparison from left to right. This means, that the first letter must be weighed most: "azzzzzz" is "smaller than" (="before") "b".

- Excel's 15 significant digits in a number result in 10 significant letters of the 26-letter alphabet. Later figures (11+) must be tolerated falling down the table. This can cause troubles on sorting "US Air Force Captain" against "US Air Force Lieutenant".

B1: =SUMPRODUCT((CODE(MID(LOWER(A1)&REPT("a",
MAX(,10-LEN(A1))),ROW(\$1:\$10),1))-97)*26^(10-ROW(\$1:\$10)))
calculates exclusively these 26 letters, no other characters included.

Upper cases, spaces, hyphens and 8-bit ASCII's will not be sorted right into it. You need to suppress or convert all these in the case they might appear.

Having B1 selected, name "R" with the formula
R: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOWER(A1)," ",),"-",),",",),"ä","ae"),"ö","oe"),"ü","ue"),"ß","ss")

(considering specific German needs for proper sorting. You might need to replace or extend these substitutions for your individual task. By using Insert Name Define for named formulas, you can extend the maximum of 8 nested functions per formula by cascading such names. xl2007 nests up to 64 functions, preventing you to do the steps before)

and use this R inside the formula before:
B1: =
SUMPRODUCT((CODE(MID(R&REPT("a",
MAX(,10-LEN(R))),ROW(\$1:\$10),1))-97)*26^(10-ROW(\$1:\$10)))

Watch the German Name-First Name example "Häßler-Meier, Thomas", being transformed to "haesslermeierthomas" and significantly cut after "haesslerme" into the number 38.044.432.451.120. Counter-check the function by converting "haesslermf" into 38.044.432.451.121.

You can even transform existing numbers between strings as being sorted behind the former strings here:

B1: =IF(ISNUMBER(A1),A1*10^5+10^15,
SUMPRODUCT((CODE(MID(R&REPT("a",
MAX(,10-LEN(R))),ROW(\$1:\$10),1))-97)*26^(10-ROW(\$1:\$10))))