Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users

Dashboard
Notifications
Mark all as read
Q&A

In a column, set populated cells to 1 and empty cells to 0

+0
−0

In an as yet unanswered Q from early this year a user of Stack Overflow asked:

how to fill all non-empty cells of a column by 1 and the empty cells by 0. It's an xlsx file open with LibreOffice Calc.

Is this possible with a formula?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comments

1 answer

+2
−0

An easy way (in LibreOffice Calc 5.4.3.2) is not to attempt directly to overwrite what is already present but to create the desired results with a formula in a different column, then replace the contents of the original column with the values (not formulae) from the added column.

Assuming the data is in ColumnA, insert a new ColumnB and in B1 and copied down() to suit:

=IF(ISBLANK(A1),0,1)

then select ColumnB, Edit > Copy, select ColumnA, Edit > Paste Only > Paste Only Numbers. Now ColumnB may be deleted.

The sheet may be saved with an xlsx extension.

Double-clicking the fill handle won't work here (because of the blank cells). Instead either drag down to suit or, if very many cells, go to the last cell to be populated and enter something there (say x) then return to B1, Copy, select B2, Ctrl+Shift+Down and Yes.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

Sign up to answer this question »