How to return the text that is in one column, in another column, ignoring zero values or blanks

[Porcupine_]

I have a column that has some cells filled with text, and some blank. I want to create a separate column that returns only the cells filled with text, ignoring blanks. I've shown what I mean in a table below. Is anyone able to help with this?

Column with data	Return with formula
Text	Text
	Text
Text

<tbody>
</tbody>

Thank you very much

 

[My Aswer Is This]

Assuming we are dealing with columns "A" and "B"
Here is a vba solution.

Sub My_Data()
'Modified 3-1-18 9:00 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
b = 1
    For i = 2 To Lastrow
        If Cells(i, 1).Value <> "" Then Cells(b, 2).Value = Cells(i, 1).Value: b = b + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[Porcupine_]

Thank you - sorry I should have said - I want to avoid using a VBA solution, and just make it work with formulas - do you know if this is possible?

 

[My Aswer Is This]

Thank you - sorry I should have said - I want to avoid using a VBA solution, and just make it work with formulas - do you know if this is possible?

There may be a formula but if there is I do not know what it is.
I do more Vba then formulas.

Maybe someone else here at Mr. Excel will be able to help you.

 

[MARZIOTULLIO]

How many rows ?

 

[Porcupine_]

72

 

[MARZIOTULLIO]

	A​	B​
1​	a	a
2​		b
3​		c
4​	b	d
5​		f
6​		g
7​		h
8​	c	i
9​		j
10​		k
11​		l
12​		m
13​	d
14​
15​
16​
17​
18​
19​
20​	f
21​
22​
23​
24​
25​
26​	g
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​	h
37​
38​
39​
40​
41​
42​
43​
44​	i
45​
46​
47​
48​
49​
50​
51​
52​
53​
54​	j
55​
56​
57​
58​
59​
60​
61​	k
62​
63​
64​
65​
66​	l
67​
68​
69​
70​
71​
72​	m

<tbody>
</tbody>

B1=IFERROR(INDEX($A$1:$A$72,SMALL(IF($A$1:$A$72<>"",ROW($A$1:$A$72)-ROW($A$1)+1),ROWS($A$1:A1))),"")

control+shift+enter copy down

 

[Porcupine_]

That looks to be exactly what I need, but for some reason it isn't working. Would it be affected if the text in column A are coming via a formula?

 

[Porcupine_]

It seems to return the text in the first row, if there is text there, but doesn't return anything else.

 

[My Aswer Is This]

That looks to be exactly what I need, but for some reason it isn't working. Would it be affected if the text in column A are coming via a formula?

It works for me.
Put = in cell B1
Then paste in the formula
Then press Ctr+Shift+Enter

Then to continue down column just drag the cell down.
Do this by clicking on bottom right corner of cell with formula you should see a + appear and drag that down.