How to return just the cell that contain a text ?

[Inacio11]

Supose the following sheet:

Column A	Column B	Column C	Column D	Column E
Saldo	-	-	-	Saldo
-	Transferencia	-	-	Transferencia
-	-	-	Gastos	Gastos
-	-	Credito	-	Credito

<tbody>
</tbody>

Which formula to use to get on column "E" just the final text (different from "-")

Thanks in advance !
Inacio

 

[sheetspread]

If they're actually dashes:

Excel 2010
	A	B	C	D	E
1	Column A	Column B	Column C	Column D	Column E
2	Saldo	-	-	-	Saldo
3	-	Transferencia	-	-	Transferencia
4	-	-	-	Gastos	Gastos
5	-	-	Credito	-	Credito
Sheet3

Cell Formulas
Range	Formula
E2	{=INDEX($A2:$D2,MATCH(TRUE,$A2:$D2<>"-",0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

LOOKUP("z",IF(A2:D2="-",0,A2:D2)) ctrl-shift-enter works too

If zeroes:

Excel 2010
	A	B	C	D	E
1	Column A	Column B	Column C	Column D	Column E
2	Saldo	0	0	0	Saldo
3	0	Transferencia	0	0	Transferencia
4	0	0	0	Gastos	Gastos
5	0	0	Credito	0	Credito
Sheet3

Cell Formulas
Range	Formula
E2	=LOOKUP("z",A2:D2)

 

[Inacio11]

Hello
Runs perfect !
Thanks very much !

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
2	Saldo	-	-	-	Saldo
3	-	Transferencia	-	-	Transferencia
4	-	-	-	Gastos	Gastos
5	-	-	Credito	-	Credito
Sheet1

In E2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/($A2:$D2<>"-"),$A2:$D2)

 

[jtakw]

Hi,

If using last formula from Post #2 , I'd suggest this modification:

Book1
	A	B	C	D	E	F
1	Column A	Column B	Column C	Column D	Column E
2	0	Zebra	0	0	Zebra	#N/A
3	0	Transferencia	0	0	Transferencia	Transferencia
4	0	0	0	Gastos	Gastos	Gastos
5	0	0	Credito	0	Credito	Credito
6
7					Modified	From Post #2
Sheet119

Cell Formulas
Range	Formula
E2	=LOOKUP(REPT("z",255),A2:D2)
F2	=LOOKUP("z",A2:D2)

 

[Inacio11]

Hello again,
After some time running with the formula "LOOKUP(9.99999999999999E+307,1/($A2:$D2<>"-"),$A2:$D2)" I realized some results errors. The contents from range A~D comes from other formulas (when I copy and paste the contents range as values the results becomes correct). I suppose the data from range A~D (built by formulas) is affecting the results.
Any idea how to solve it ?
Thanks in advance

 

[Aladin Akyurek]

Hello again,
After some time running with the formula "LOOKUP(9.99999999999999E+307,1/($A2:$D2<>"-"),$A2:$D2)" I realized some results errors. The contents from range A~D comes from other formulas (when I copy and paste the contents range as values the results becomes correct). I suppose the data from range A~D (built by formulas) is affecting the results.
Any idea how to solve it ?
Thanks in advance

What are the incorrect results that you get?

 

[Inacio11]

Hard to explain with no visualization of the sheet....but for example if I type any word in any cell....the results changes (does not make sense ???).
Suppose that I found a solution using the formula "{=INDEX($A2:$D2,MATCH(TRUE,$A2:$D2<>"-",0))}" posted by sheetspread.....no errors till now !
Thanks

 

[Aladin Akyurek]

Hard to explain with no visualization of the sheet....but for example if I type any word in any cell....the results changes (does not make sense ???).
Suppose that I found a solution using the formula "{=INDEX($A2:$D2,MATCH(TRUE,$A2:$D2<>"-",0))}" posted by sheetspread.....no errors till now !
Thanks

{=INDEX($A2:$D2,MATCH(TRUE,$A2:$D2<>"-",0))}

returns the first value which is not "-".

=LOOKUP(9.99999999999999E+307,1/($A2:$D2<>"-"),$A2:$D2)

returns the last value which is not "-".


You must try to explicit what you result you get instead of the result that you expect.