Extract Text Value From Array

[Shteven]

Hello all. Two questions that I thought would be easy, but I am stumped. Sorry, cannot attach a workbook:

1) If I have an array containing zeros and text values like the ones below (alpha characters only), how can I extract a single text value? I don't know where the text will appear, I don't know what the text will be, and it may appear once or multiple times.

{0, 0, ABC, 0, ABC} should return ABC
{DEF, DEF, 0, 0, DEF} should return DEF

Additional question:
My current data set will only allow either ABC or DEF to appear in each array, never both. However, since I am unfamiliar with the data, I don't want to eliminate the possibility that an array could contain both in the future. How would you modify the first formula to return something like "Multiples" in case I run into that issue?

Thank you in advance!

 

[bosco_yip]

Ans 1 :

=INDEX({0,0,"ABC",0,"ABC"},MATCH(TRUE,ISTEXT({0,0,"ABC",0,"ABC"}),0))

and,

=INDEX({"DEF","DEF",0,0,"DEF"},MATCH(TRUE,ISTEXT({"DEF","DEF",0,0,"DEF"}),0))

Ans 2 :

=SUBSTITUTE(INDEX({0,0,"ABC",0,"ABC"},MATCH(TRUE,ISTEXT({0,0,"ABC",0,"ABC"}),0)),"ABC","Multiples")

Regards

 

[Shteven]

Ans 2 :

=SUBSTITUTE(INDEX({0,0,"ABC",0,"ABC"},MATCH(TRUE,ISTEXT({0,0,"ABC",0,"ABC"}),0)),"ABC","Multiples")

Regards

Sorry, maybe I wasn't clear. I am looking for a formula for these scenarios, and I do not know what the text value(s) will be:

{0, 0, ABC, 0, ABC} should return ABC
{DEF, DEF, 0, 0, DEF} should return DEF
{0, ABC, 0, DEF, ABC} should return Multiples

 

[bosco_yip]

I am looking for a formula for these scenarios, and I do not know what the text value(s) will be:
{0, 0, ABC, 0, ABC} should return ABC
{DEF, DEF, 0, 0, DEF} should return DEF
{0, ABC, 0, DEF, ABC} should return Multiples

1] Data housed in A1:E1

2] In F1, formula :

=IF(SUMPRODUCT(1/COUNTIF(A1:E1,A1:E1))<3,IFERROR(INDEX(A1:E1,MATCH(TRUE,INDEX(ISTEXT(A1:E1),0),0)),""),"Multiples")

Regards

 

[Shteven]

1] Data housed in A1:E1

2] In F1, formula :

=IF(SUMPRODUCT(1/COUNTIF(A1:E1,A1:E1))<3,IFERROR(INDEX(A1:E1,MATCH(TRUE,INDEX(ISTEXT(A1:E1),0),0)),""),"Multiples")

Regards

My knowledge of arrays isn't the best, so I'm having trouble applying this solution. First solution works fine.

My input array is the result of another formula, not a cell range, so I changed A1:E1 to my formula to test, but it returns an error. Should your solution still work?