From one row take the last cell with text

[dimzev]

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

[PYS]

Hi

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

Of course, adapt ; according to your Tools▸Options▸Calc▸Formula▸Separators▸Function.

See LastCol.ods

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

Explanations:

• 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

[EDIT]

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

Regards

[dimzev]

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 ?

[PYS]

@dimzev - have you check:

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

[dimzev]

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

[PYS]

@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.