Value from row

[Tariq123]

If a row contain different value. Like. A1=0, B1=3, C1=0 D1=4. Then i need first value which is greater then 0. In another cell.
0 3 0 4
I need first value greater then 0, means 3 in A5 cell.
And 2nd value greater then zero in A6 which is 4.
How its possible through formula

 

[ExceLoki]

try this

=TRANSPOSE(SMALL(IF(A1:D1>0,A1:D1),ROW(INDIRECT("1:"&COUNTIF(A1:D1,">0")))))

 

[Tariq123]

This is not working.

 

[Tariq123]

I need as below
0 3 5 0 4
First value 3
Second value 5
Third value 4
Formula should ignore the 0 value.

 

[ExceLoki]

your first example had 4 values, your recent reply has 5 values. below is the same formula showing each of your examples.
-------------------

Values from row no 0.xlsx
	A	B	C	D	E	F	G	H
1	0	3	0	4		3	4
2	0	3	5	0	4	3	4	5
Sheet1

Cell Formulas
Range	Formula
F1:G1,F2:H2	F1	=TRANSPOSE(SMALL(IF(A1:E1>0,A1:E1),ROW(INDIRECT("1:"&COUNTIF(A1:E1,">0")))))
Dynamic array formulas.

------------------
if this is still not working for you, please share what you are getting so can figure out why.

 

[Tariq123]

Hi
Thank you for your reply.
Sir your formula result is 0.
I tried below formula but this for is only for Ist vale but i required second and third also.
=index(A1:E1,MATCH(TRUE,INDEX(A1:E1)<>0,),0))

 

[Leo Skywalker]

Try this in G1:

=MID(SUBSTITUTE(TEXTJOIN("",TRUE,$A$1:$E$1),"0",""),COLUMNS($A$1:A1),1)

And copy to the right for as many cells as needed.

 

[Peter_SSs]

@ExceLoki , @Leo Skywalker
The OP's profile shows Excel 2013 so they will not have 'spill' ability or TEXTJOIN function.

If a row contain different value. Like. A1=0, B1=3, C1=0 D1=4. Then i need first value which is greater then 0. In another cell.
0 3 0 4
I need first value greater then 0, means 3 in A5 cell.
And 2nd value greater then zero in A6 which is 4.
How its possible through formula

Try this

22 11 17.xlsm
	A	B	C	D	E
1	0	3	0	4
2
3
4
5	3
6	4
7
8
9
>0

Cell Formulas
Range	Formula
A5:A9	A5	=IFERROR(INDEX(A$1:E$1,AGGREGATE(15,6,COLUMN(A$1:E$1)/(A$1:E$1>0),ROWS(A$5:A5))),"")

 

[Leo Skywalker]

Peter,

You're right... no spilling, no TEXTJOIN
Here are other two options, replacing TEXTJOIN with manual concat

If you want to copy to the right, results in the same row:

=MID(SUBSTITUTE($A$1&$B$1&$C$1&$D$1&$E$1,"0",""),COLUMNS($A$1:A1),1)

If you want to copy down, results in the same column:

=MID(SUBSTITUTE($A$1&$B$1&$C$1&$D$1&$E$1,"0",""),ROWS($A$1:A1),1)

 

[Tariq123]

Hi
Greeting

I tried below formula. Its working if the value in single digit. If the is 15 then it will write 1 in one cell and 5 in next cell.

=MID(SUBSTITUTE($A$1&$B$1&$C$1&$D$1&$E$1,"0",""),ROWS($A$1:A1),1)