In my previous blog post,

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:

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.

E. Joseph BilloDavid:

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

David HagerPost authorThanks!

Joe BilloDavid:

Recently retired chemist? Me too. I have a suggestion for you.

Joe Billo

David HagerPost authorSure. Go ahead. Thanks! dhExcel1@gmail.com

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

Pingback: #Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges | Excel For You