formula =Cell returns 0 if cell is empty - can it be blank?

**jowes** · 11-05-2013, 06:07 AM

Hi all,

I would like a cell to give the value of another cell and remain blank if the reference cell is blank. The formula ='cell' (where 'cell' is the reference cell) works well, except when the reference cell is empty - in that case it returns 0 (zero).

What can I do to make the formula return a blank when the reference cell is blank?

Thanks

**TMS** · 11-05-2013, 06:12 AM

You can use something like:

Formula:

Please Login or Register  to view this content.

or, if the data is always alphanumeric, you can use:

Formula:

Please Login or Register  to view this content.

If you use the second one with a numeric value, it will make it a text field. That is, it will still look like a number but it will be left aligned and some numeric calculations won't work.

Regards, TMS

**ben_hensel** · 11-05-2013, 06:13 AM

=IF( ISBLANK(cell), "", cell)

That will fill the cell with an empty (zero characters) text string. So it's not technically blank (for example, ISBLANK("") returns FALSE), but it looks that's the typical way to deliver what you're after.

**Ursul** · 11-05-2013, 06:14 AM

Let's say A1 = 100, A2 = (blank), A3 = 200. In cell B1 use the IF function i.e. something like this =IF(A1="","",A1) and drag it down to B3 and now B1 = 100, B2 = blank, B3 = 200

**TMS** · 11-05-2013, 07:43 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**TMS** · 11-05-2013, 08:36 AM

@jowes: thank you for marking the thread as solved. It is considered good etiquette and practice to provide feedback, possibly a thank you, to those who have helped you solve your problem ... especially, so quickly: three (3) answers and explanations within seven (7) minutes of asking.

A thank you and, ideally, positive reputation points might just encourage those people to consider helping you again in the future. Then again, ...