From one row take the last cell with text

i have a table with many rows and 5 (A,B,C,D,E) columns.
the cells from each row can be empty or have text .
i want in F column take as result :
if all cells on row is empty take in F column empty cell
if in one row have one cell with text , take in F column cell this text

if in one row have 2 or more cells with text i need take as result on F column the text from right column (from E if no empty, from D if no empty, from C if no empty …
this is my problem ?
if we want go further in F column keep the formating text LOupload22.ods


For example: =IFNA(INDEX(A2:E2;1;MATCH(".*";A2:E2;1));"")

Of course, adapt ; according to your ToolsOptionsCalcFormulaSeparatorsFunction.

See LastCol.ods

Note: you need to enable ToolsOptionsCalcCalculateEnable regular expressions in formulas


  • MATCH If Type = 1 the index of the last value that is smaller or equal to the search criterion is returned.
  • INDEX uses this position to find in the range
  • IFNA used to return nothing if MATCH = #NA


You can apply conditional formating using: STYLE(IFNA(INDEX($I$2:$I$6;MATCH(".*";A2:E2;1));""))

  • Create style to apply to the cols (COLA, COLB, etc. in my example)
  • MATCH: same as above, gives the last col.
  • INDEX: find in the range of style name
  • STYLE: applies…

See col. F in LastColFormat.ods


Yes i download your fille and i see it works !!!
But when i put it in my fille (see i upload) dont work…
what i am doing wrong ?

@dimzev - have you check:

Note: you need to enable Tools▸Options▸Calc▸Calculate▸Enable regular expressions in formulas

ooo YES thank you !!! now i see …
can you compose the results F and G column (i need keep the formating text from each cell …
Thank you again

@dimzev sorry, I forgot the second part of the question (about formating). You can simply use same idea of formula for a conditional formating: see edit in my answer.