0

I am an excel novice. I have started working with data strings of over 1000 rows. Some cells in a column have 0 values and must be replaced with the value of the cell to the left. Currently, I copy and paste to get the correct data. This takes hours. Surely that must be a better way or a function I can use? In the picture below. All 0 values in column E must be replaced with the value of the cell in column D. The same goes for column G, the 0 values in column G must be replaced with the value of the cell in column D. So. E4 0 replace with the value in D4. And G5 0 replace with the value in D5.

0 value in E4 replace with value of D4

Can anyone please help with a formula? I use Excel 2019 professional.

Improve this question
3
  • 1
    You can't have a formula replace an existing value (i.e. the cell either has the formula or the value, but can't have both). That said, you could use a helper column, or some VBA to accomplish what you want
    – cybernetic.nomad
    Commented Nov 2, 2022 at 16:50
  • Please post your data as text, not image.
    – Toto
    Commented Nov 2, 2022 at 16:54
  • Consider using a VBA to accomplish this requirement.
    – Ceasar
    Commented Nov 3, 2022 at 1:27

1 Answer 1

Reset to default
0

Use a second Sheet and "duplicate" your data there by typing =Sheet1!A1 (e.g. =$Sheet1.A1 in LibreOffice) in the upper left cell and copy/paste that for all (relevant!) data-occupied cells.

In this new Sheet you will then have the option to fill in formulas;
as in =IF(Sheet1!A1<>0; Sheet1!A1; Sheet1!B1)
to copy the value from B1 instead, if A1 is 0.

; comes from my Swedish locale, your locale might require ,

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .