Pick up text/value

[Jonathan Lee]

Hi Shifus,

Need you help here.

I have a range of cells in columns. Unfortunately each row the values were placed in different columns. How can I put all into one column?

Example below, texts are in A, B and C. I need to put all into D.

Thanks.

	A	B	C	D
1	apple			apple
2		orange		orange
3			grapes	grapes

 

[Peter_SSs]

Further formula options.

If the columns with the text values do not contain formulas, then you could use a formula like I have in col U.
If they do contain formulas and you would prefer to avoid the Ctrl+Shift+Enter formula confirmation, you could use a formula like I have in col V

21 09 15.xlsm
	A	B	C	D																T	U	V
1	apple																				apple	apple
2		orange																			orange	orange
3			grapes																		grapes	grapes
Text Value

Cell Formulas
Range	Formula
U1:U3	U1	=LOOKUP("ZZZ",A1:T1)
V1:V3	V1	=INDEX(A1:T1,MATCH(TRUE,INDEX(A1:T1<>"",0),0))

 

[Jonathan Lee]

Further formula options.

If the columns with the text values do not contain formulas, then you could use a formula like I have in col U.
If they do contain formulas and you would prefer to avoid the Ctrl+Shift+Enter formula confirmation, you could use a formula like I have in col V

21 09 15.xlsm
	A	B	C	D																T	U	V
1	apple																				apple	apple
2		orange																			orange	orange
3			grapes																		grapes	grapes
Text Value

Cell Formulas
Range	Formula
U1:U3	U1	=LOOKUP("ZZZ",A1:T1)
V1:V3	V1	=INDEX(A1:T1,MATCH(TRUE,INDEX(A1:T1<>"",0),0))

Thanks. Exactly what I needed.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.