Select Page

GOAL:

To pull digits from alphanumeric strings. If length and position of numeric data is fixed we can extract digits using built in functions of Excel e.g. LEFT, RIGHT and MID. Our goal is to develop a formula/ function by which we can pull digits from alphanumeric string irrespective of its size and position.

From the GOAL above we may have two scenarios (i) All digits are together in one group (ii) Digits are scattered

ALGORITHIM:

We can use 2nd option for all cases; however, for better understanding, let us develop different formulas based on scenarios.

In case all digits are together: Find out the position of first digit or numeric value, within alphanumeric string. After ascertaining position of first digit, create an array of 1st digit found, 1st digit found plus next character, 1st digit found plus next two characters and so on till the length available. Convert it to number and pick the largest value available in this array.

If digits are scattered: Select all numeric values in an array, create other array of 1, 10, 100…… and multiply these arrays to get place value of numbers picked and add the same to reach the required result.

One other option is to develop a user defined function (UDF) to pick the numeric values from alpha numeric string.

FORMULA:

When all digits are together:

If alpha-numeric string is in cell “A1” insert below formula in cell “B1” (“A1” and “B1” are used for example only, this’ll work in any 2 sets of cells)

=LOOKUP(99^99,–(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT(“1:”&LEN(A1)+1)))))

Please be informed that the above formula will pick only first bunch of digits.

When digits are scattered:

In the above scenario insert below formula in cell “B1”:

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(1:999),1)/10,””))

Please do remember that above formula is array one and you must confirm it by pressing “Ctrl+Shift+Enter” together.

Due to use of “NPV” function Excel will convert the cell formatting as “Currency”, we may change the same as required.

“Two things are infinite: the universe and human stupidity; and I’m not sure about the universe.”
― Albert Einstein

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing! 