Extracting a number from a string

Someone asked me this question. I found some answer on the web but it was really complex.

I thought that maybe using the CONCAT function in the context of an array formula could do the trick.

If you don't see CONCAT in your Excel version, it is because you don't have Excel 2016 and if you have Excel 2016 and still don't see it , it is because you don't have Office licensed through O365. CONCAT is one of the functions added after the release of Excel 2016 and available only to O365 subscribers.

The CONCAT function is similar to CONCATENATE but it can get ranges as arguments and will concatenate all strings in the range. So instead of =G3&H3&I3&J3&K3...Z3 you can write =CONCAT(G3:Z3)

That's neat but I thought that I can feed CONCAT single characters using a combination of MID and IF from an array formula.

In cell K7 I have the value "LTEdfghikj-2015120112/abc"

My goal is to extract the number stored inside the string.

The formula to do it is :

={VALUE(CONCAT(IF((MID($K$7,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},1)>="0")*(MID($K$7,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},1)<="9"),MID($K$7,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},1),"")))}

Seems daunting but I will break it into pieces to make it easier to understand.

A function within an array formula will run once for each element in a range or an array in the formula. The array in this case is {1,2,3...25} so the MID function will return one character at a time from the string which is up to 25 characters long.

Each character will be extracted twice and examined by the IF functions to see if it is a digit. The two IF results are themselves arrays of TRUE/FALSE values and are multiplied to apply and AND condition (The AND function doesn't work in an array formula)

If the character is a digit it is returned as is and if not a null string is returned. All the 25 results are concatenated by the CONCAT function and the result is converted into a number and the value is 2015120112.

Two improvements can be applied:

We can replace the long arrays {1,2,3... with the ROW function and give it a range of 25 rows 1 through 25. The ROW function will return the required array.

={VALUE(CONCAT(IF((MID($K$7,ROW($1:$25),1)>="0")*(MID($K$7,ROW($1:$25),1)<="9"),MID($K$7,ROW($1:$25),1),"")))}

This is much shorter but beware that if you insert/delete rows in the first 25 rows the formula will be adjusted.

The last improvement is for the ultimate geeks among us. Instead of being dependent on the length of the string and adjusting the arrays to the size of the maximum string, I can adjust the array sizes to the length of the string.

={VALUE(CONCAT(IF((MID($K$6,ROW(INDIRECT("$1:$"&LEN($K$6))),1)>="0")*(MID($K$6,ROW(INDIRECT("$1:$"&LEN($K$6))),1)<="9"),MID($K$6,ROW(INDIRECT("$1:$"&LEN($K$6))),1),"")))}

The indirect function uses the length of the string to determine the size of the array.

As always use caution with INDIRECT because it is difficult to audit and it is a volatile function so it calculates on every change in any open Workbook.