# 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 C:\fakepath\LOupload22.ods

edit retag close merge delete

Sort by » oldest newest most voted

Hi

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

Of course, adapt ; according to your ToolsOptionsCalcFormulaSeparatorsFunction.

Note: you need to enable ToolsOptionsCalcCalculateEnable 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 C:\fakepath\LastColFormat.ods

Regards

more

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 ?

( 2017-10-01 10:40:55 +0200 )edit

@dimzev - have you check:

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

( 2017-10-01 13:48:33 +0200 )edit

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

( 2017-10-01 15:20:39 +0200 )edit

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

( 2017-10-02 17:14:27 +0200 )edit