Copy long column but skip empty cells

[ve3oat]

I have a long column with some numerical data and many empty cells and need to copy and compress it (ignoring the empty cells) to use in another spreadsheet. It is a monthly chore and is tedious to find and copy the around 30 cells containing data amongst the over 4000 empty cells. The cells with data are the result of a formula.

I have tried copying the entire column and then using Paste Only Numbers, but that didn’t work. (The whole column was pasted, empty cells and all.)

I have also tried Paste Special, enabling Numbers, Formulas and the “skip empty cells” option, but surprsingly that didn’t work either (same result – whole column pasted).

I must be missing some vital step but do not know what it is. Any help would be appreciated. Thanks very much.

Using LO v5.4.5.1 with Win7 Pro.

[Lupp]

-1- Select the column.
-2- Call F&R (Ctrl+H).
-3- Enable RegEx.
-4- Search for “.” (without the quotes).
-5- Find All
-6- Copy
-7- Got to target cell.
-8- Paste.

You may also use the AutoFilter and unselect (empty) before copying.
Select All again before pasting to filtered rows in adifferent column.

Using the Standard filter you can filter for the Smallest % with 100 (Don’t enter the percent sign!)
Choose the option to copy the results elsewhere. This avoids filtering by hiding rows.

See also this demo. It shows a variant using the Standard filter with RegEx.

[ve3oat]

Thank you, @Lupp, your first suggested method worked “like a charm” and will save me lots of time. I have never used the autofilter before and could not get that method to work, though I expect it is just my unfamiliarity with that. I will explore some more there.

Curious that the Paste Special plus skip empty cells method did not work for me. It seemed so obvious and straightforward.

Thank you again for your help.

[Lupp]

The “disobedience” of Paste Special... with Skip empty cells enabled is ostensible. In fact the functionality is badly indicated. The actual meaning is that cells in the target range having content, and by position corresponding with empty cells of the source range preserve their content. They are not “overwritten with empty”. You may suggest a better wording - and you may suggest an enhancement to (also) implement the functionality you expected.

[ve3oat]

Dear #Lupp, as usual your description of the situation is full of wonderful insight.

[Lupp]

@ve3oat: I would forgo. But If you actually consider to file an enhancement request, I may suggest the following wordings:
-1- Current “Skip empty cells” becomes “Don’t delete by pasting”
-2- What you expected: “Paste compacted”
[-3- A not yet discussed, but also valuable variant might be to only paste into previously blank cells, and let already present content unchanged. “Only paste to blank”]
Short and clear? Model English not sought.

[Lupp]

Anyway -1- and -2- would suffer from another blur due to traditional ambiguity in terms mirrored in the functionality of spreadsheets: There is no cell content actually equivalent to the empty text as a formuöla result. If you copy a cell answering TRUE when asked =ISTEXT() because it has that empty text as a formula result, and you Paste Special... it selecting strings and values only, you get a blank cell answering FALSE to the mentioned question.
Better not touch it?

[ve3oat]

Thank you, @Lupp, I haven’t decided yet what to do. After years of using only QuattroPro for my spreadsheets, I am now constantly amazed at the inner sophistication and complexity of LibreOffice.