Formula to grab the first non-zero value in a row of data, and then every value after that.

[SanFelippo]

Hello,

So I will try and explain this as easily as I can using an example. Lets say I have this data in 3 rows:

0 0 0 0 0 0 5 8 0 9 4 1 5
0 0 0 0 1 5 6 7 9 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 7 9

The formula I would need would return the following results in 3 other rows below what is seen above:

5 8 0 9 4 1 5
1 5 6 7 9 0 0 0 1
7 9

I haven't had any luck so far with this, so any help would be appreciated.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0	0	0	0	0	0	5	8	0	9	4	1	5
2	0	0	0	0	1	5	6	7	9	0	0	0	1
3	0	0	0	0	0	0	0	0	0	0	0	7	9
4
5	5	8	0	9	4	1	5	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !
6	1	5	6	7	9	0	0	0	1	#REF !	#REF !	#REF !	#REF !
7	7	9	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !	#REF !
Sheet6

Cell Formulas
Range	Formula
A5	{=INDEX($A1:$M1,MATCH(TRUE,$A1:$M1<>0,0)+COLUMN(A1)-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Add an iferror to replace the #REF ! with ""s or F5(Goto)-errors to highlight then delete

I think textjoin works too if you have a later Excel version

 

[SanFelippo]

Sorry, I never got a notification this was answered. This works perfectly. Thank you

 

[sheetspread]

 

[jasonb75]

It can be done without the need for an array or an exact match if your example is a true reflection of your real data.

This will work with positive numbers only, it could be adjusted to work with negatives if needed.

=IFERROR(INDEX($A1:$M1,MATCH(1E-100,$A1:$M1)+COLUMNS($A5:A5)),"")

If your data contains a mixture of positive and negative values then you would need to use the array formula suggested originally.