How to retrieve a non-blank text value in an array of text values where only one value is non-blank text

[landish]

Hi everyone, I've got an array of text values like this {"","","","words,""} HOWEVER I don't know "words" value's position in the array it might look {"","","","",words} or {"","words,"","",""}. I need to retrieve only "words" value using formula functions. It can be done either by merging all the values in the array or by sorting values so "words" value is in the first position then I can use T() function. But I can't find any function or combination of functions to do this. Thank you in advance.

 

[Jon von der Heyden]

Try;

=LOOKUP(2,1/({"";"";"words";""}<>""),{"";"";"words";""})

 

[Aladin Akyurek]

Hi everyone, I've got an array of text values like this {"","","","words,""} HOWEVER I don't know "words" value's position in the array it might look {"","","","",words} or {"","words,"","",""}. I need to retrieve only "words" value using formula functions. It can be done either by merging all the values in the array or by sorting values so "words" value is in the first position then I can use T() function. But I can't find any function or combination of functions to do this. Thank you in advance.

=LOOKUP(REPT("z",255),Reference)

is the fastest formula to fetch that single non-blank text bit wherever it is.

Note. Reference can be either an array like {"","","Kazan",""} or a range like A2:A10 or A:A.

 

[landish]

Amazing, it works! Thank you very much!!!

 

[Aladin Akyurek]

Amazing, it works! Thank you very much!!!

If you do have formula blanks (formula cells returning ""), the suggestion I made won't work as it would return "" if that's the last value in the reference of interest. Jon's suggestion would indeed return the last non-blank text as would the following:

=LOOKUP(9.99999999999999E+307,SEARCH("?*",A1:H1),A1:H1)

 

[pgc01]

Hi

And another one, similar to Aladin's:

=LOOKUP(0,-SEARCH("?",A1:H1),A1:H1)

 

[pgc01]

or

=INDEX(A1:H1,MATCH("?*",A1:H1,0))

 

[landish]

Yes, actually I didn't get how to solve this problem in your way. I was going to ask why I need to use nested REPT function. But anyway thanks, I was searching for the main function which is LOOKUP that makes it possible to retrieve data from array. You and Jon both used this function. I am really grateful to Jon. His example works best.

If you do have formula blanks (formula cells returning ""), the suggestion I made won't work as it would return "" if that's the last value in the reference of interest. Jon's suggestion would indeed return the last non-blank text as would the following:

=LOOKUP(9.99999999999999E+307,SEARCH("?*",A1:H1),A1:H1)

 

[landish]

Thanks Jon, this is exactly what I need! This is the best solution of this problem

Try;

=LOOKUP(2,1/({"";"";"words";""}<>""),{"";"";"words";""})

 

[landish]

Thanks everyone for sharing your ideas on how to solve this problem!