choose a cell in a range which has a text (there is only one)

[flying man]

Resulting from other calculations, I have a group of cells in which one
contains a text and all the others are zero (or " ").
I need to make a given cell to assume the text of the non-zero cell on that
group.

If I had number insted of text, the function "Max" would work, but it does
not with text content.

 

[Mike H]

Hi,

What is a 'group' of cells? This works with a single column or row

=INDEX(A1:A11,MATCH(MAX(LEN(A1:A11)),LEN(A1:A11),0),1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Pete_UK]

If the cells which do not contain the text value actually had "" (not
" " nor zero) from their respective formulae, then you could just do
this:

=A1 & A2 & A3 & A4 & A5 & A6 ... and so on.

Hope this helps.

Pete