I have an Excel file that contains hundreds of rows and columns of delimited data. Every cell looks like these two sample cells, and it used alphas and symbols to represent a specific result:
2-0*0*8-15-8-T4-<2-D4-C4-$4-6-4->2-X4-^6-%-|0|-/P4
4-0*0*3-13-5-K3-<2-S3-C3-$3-11-7-999-M3-^1-+-|4|-/W3
Each piece of the data within the delimited structure refers to a testing result and each test is contained on a single row, ranging from about 50 to 600 cells.
My challenge is I need to come up with a way to get an average of the numeric values contained in the 16th delimited segment, the number contained within the pipes, for example "|0|" and "|4|" in the two examples above.
I have been using formulas such as this to count all the cells in a row that have both "S" and "/W" in the cell:
=COUNTIF('Test Data'!2:2,"*S*/W*")
But I cannot figure out how to get the average of the numbers contained within the pipes "|4|" etc.
Any help is greatly appreciated.
As @Max pointed out, TEXTSPLIT seems to be the best solution to your problem.
If you have to avoid TEXTSPLIT, here is an alternative:
=AVERAGE(IFERROR(VALUE(MID(data,SEARCH("|",data,1)+1,SEARCH("|",data,SEARCH("|",data,1)+1)-SEARCH("|",data,1)-1)),""))
which can be slightly shortened to
=AVERAGE(IFERROR(VALUE(LET(a,SEARCH("|",data,1),MID(data,a+1,SEARCH("|",data,a+1)-a-1))),""))
where 'data' is the array of rows and columns containing the test codes.