[SOLVED] How to copy a column data in another column, ignoring the blanks

Hello,

Let me get right into my question:

In column A I have a list of datas, but those datas are not corectly sorted, it looks like :

875
47

541


9855

44
2




845


54

The datas are separated by blank cases, the space between 2 data is totally random, sometimes 2 datas will be right next to each other, sometimes i will have 5 empty cases between 2 datas (verticaly)

In the column B I want to keep this exact same datas but without the empty spaces
so my column B would look like :

875
47
541
9855
44
2
845
54

How can I do that ?

Thank you very much for your help.
Max

Hello,

Thank you very much for your help.
Unfortunatly it does not seem to work, my datas are in the column A1/A1000, I put your formula in B1, then copy down, and nothing displayed, the cases are empty.

I should have told this in my previous mail but my datas are not numbers but words (sometimes full sentences).
I thought maybe it was not relevant because content is still content, but I remember now that some forumlas only works with numbers.

EDIT: I tried your formula with numbers and it does not seem to work either.
Do I do something wrong ?

I have excel 2013 by the way

Thanks

Here is a VBA solution.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button