Shift non zero cells with text in next coloum up

[A Thayuman]

Hi I hope someone can help me with a formula where I can shift cells with text in the next coloum up without having to use the filter option

Thanking you'll in advance, Regards,
Anil

0	W
0	EH
0	EH
0	EH
0	N
0
0
W
EH
EH
EH
0
N
0
0

 

[MARZIOTULLIO]

B2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/(ISTEXT($A$2:$A$100)),ROWS($C$1:C1))),"")

Copy down

 

[A Thayuman]

B2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/(ISTEXT($A$2:$A$100)),ROWS($C$1:C1))),"")

Copy down

Thank you Marziotullio works perfectly, your help is much appreciated

 

[Peter_SSs]

@A Thayuman
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

For example, if you have a recent Excel version you can do this with a much shorter formula and there is no need to copy the formula down.

24 10 11.xlsm
	A	B
1
2	0	W
3	0	EH
4	0	EH
5	0	EH
6	0	N
7	0
8	0
9	W
10	EH
11	EH
12	EH
13	0
14	N
15	0
16	0
Move up

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(A2:A16,A2:A16<>0,"")
Dynamic array formulas.

BTW, I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

 

[Peter_SSs]

Thank you Marziotullio works perfectly,

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.