Calculating Aggregation for Internal Numbers From Strings in a Range by David Hager

In my previous blog post,

https://dhexcel1.wordpress.com/2017/02/05/using-the-excel-textjoin-function-to-extract-an-internal-numeric-string-from-a-string-by-david-hager/

I demonstrated a way to efficiently extract an internal number from a string. However I thought I would try to extend this by doing the same thing to a range of strings that contain internal numbers. Using the CONCAT function I was able to combined strings from cells in a specified range.

rng=Sheet1!$A$2:$A$5 (strings as shown below).

amx2063jawe
amx203jawe
amx205631jawe
amx20563jawe

cStr=CONCAT(rng)

Note that the strings are of different length as are the internal strings of the numbers. Using a methodology to convert strings from individual cells did not help me to be able to solve the problem. So the next step in processing the long string was to convert it to an array. But what was required was a way to construct an array that contained the internal numeric strings but no non-numeric array items. The effort to solve this problem involved several dead ends and several days of work. Finally, I decided that the non-numeric items needed to be converted to commas so that the array could be converted to a comma delimited string. This was accomplished as shown below.

commaArray=IF(ISNUMBER(VALUE(MID(cStr,ROW(INDIRECT(“1:”&LEN(cStr))),1))),MID(cStr,ROW(INDIRECT(“1:”&LEN(cStr))),1),”,”)

which affords:

{“,”;”,”;”,”;”2″;”0″;”6″;”3″;”,”;”,”;”,”;”,”;”,”;”,”;”,”;”2″;

“0”;”3″;”,”;”,”;”,”;”,”;”,”;”,”;”,”;”2″;”0″;”5″;”6″;”3″;”1″;”,

“;”,”;”,”;”,”;”,”;”,”;”,”;”2″;”0″;”5″;”6″;”3″;”,”;”,”;”,”;”,”}

The use of the TEXTJOIN function was able to accomplish this as well as putting the numeric items back together as originally found in the internal strings.

commaStr=TEXTJOIN(“”,TRUE,commaArray)

affords the string

,,,2063,,,,,,,203,,,,,,,205631,,,,,,,20563,,,,
If you have read this blog post:

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

as originally created at:

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

You should remember that the following formula will convert a comma delimited string back to an array. But, I was not sure if a delimited string with so many blank fields would convert correctly, but it did!

tArray=VALUE(TRIM(MID(SUBSTITUTE(commaStr,”,”,REPT(” “,999)), ROW(INDIRECT(“1:”&LEN(commaStr)-LEN(SUBSTITUTE(commaStr,”,”,””))+1))*999-998,999)))

giving:

{“,”;”,”;”,”;”2063;”,”;”,”;”,”;”,”;”,”;”,”;”,”;203;”,”;”,”;”,”;”,”;”,”;”,”;”,”;205631;”,”;”

,”;”,”;”,”;”,”;”,”;”,”;20563;”,”;”,”;”,”;”,”}
The final step is an array formula that creates the array that can be operated on by the AVERAGE function. Of course, any aggregation can be used as desired.

=AVERAGE(IF(ISERROR(tArray),””,tArray))
Remember that you have to have the Excel 2016 version included in Office 365 in order for the TEXTJOIN and CONCAT functions to work.