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?

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Column with data[/TD]
[TD]Return with formula [/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]b[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]c[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD]l[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD]h[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

42​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

43​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

44​

[/TD]
[TD]i[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

46​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

47​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

48​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

49​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

51​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

52​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

53​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

54​

[/TD]
[TD]j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

55​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

56​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

57​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

58​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

59​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

61​

[/TD]
[TD]k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

62​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

63​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

64​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

65​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

66​

[/TD]
[TD]l[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

67​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

68​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

69​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

70​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

71​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

72​

[/TD]
[TD]m[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.