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.

Advertisements

5 thoughts on “Calculating Aggregation for Internal Numbers From Strings in a Range by David Hager

  1. E. Joseph Billo

    David:
    I liked your approach to getting a number from a string. I thought I might try a different approach, by finding the first and last position of the number characters, then using MID. I managed to make it work but the formula was much more “mega” than yours. Here’s the formula:
    {=MID($A$2,SMALL(NOT(ISERROR(MID($A$2,ROW(INDIRECT(“1:” & LEN($A$2))),1)*1))*ROW(INDIRECT(“1:” & LEN($A$2))),LEN($A$2)-E14+1),SUM(NOT(ISERROR(MID($A$2,ROW(INDIRECT(“1:” & LEN($A$2))),1)*1))*1))}.

    Notice the E14 in the second-to-last line. I was unable to replace it with the array formula
    SUM(NOT(ISERROR(MID($A$2,ROW(INDIRECT(“1:” & LEN($A$2))),1)*1))*1)
    that should go there, b/c that exceeded the allowable number of nested formulas.

    –Joe Billo

    Reply
  2. Pingback: #Excel Super Links #26 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s