Ask Your Question
0

from one row take the last cell with text

asked 2017-10-01 09:20:05 +0200

dimzev gravatar image

updated 2017-10-01 10:39:02 +0200

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-10-01 09:57:07 +0200

pierre-yves samyn gravatar image

updated 2017-10-02 17:18:26 +0200

Hi

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

Of course, adapt ; according to your ToolsOptionsCalcFormulaSeparatorsFunction.

See C:\fakepath\LastCol.ods

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

edit flag offensive delete link more

Comments

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 gravatar imagedimzev ( 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

pierre-yves samyn gravatar imagepierre-yves samyn ( 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

dimzev gravatar imagedimzev ( 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.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-10-02 17:14:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-01 09:20:05 +0200

Seen: 282 times

Last updated: Oct 02 '17