blank cell shows as a "0" in a formula

[rjr]

Hello,
I have an Excel 2003 worksheet where cell
A1 is First Name
A2 is MI ---- When they don't have a middle initial this cell is left blank.
A3 is Last Name.

However I have put a concatenate formula in another cell and whenever the
"A2" is blank it provides a "0" instead of a blank space.
A1 = Richard
A2 = T.
A3 = Jones would concatenate as Richard T. Jones

However without a middle initial I get Richard 0 Jones.
Also when there is a blank I would like to make sure it eliminates the space
and simply shows Richard Jones. But the 0 problem is the biggest. This
worksheet is used as a database for a WORD merged document.

I've posted this before but only got one answer that wasn't appropriate, so
please if anyone has an answer I really need it

Thank you
BOB

 

[rjr]

Additional that I forgot.
The A2 has a formula that refers to another cell on another worksheet. So it
refers to worksheet 2 cell a2 and this would be the one that's blank. I
think excel is picking up the formula as not being blank and reporting the
zero, but I need it to report blank if there's nothing in the original cell.
Thanks

 

[daddylonglegs]

What formula do you have in A2?

If it's a VLOOKUP, for instance then you could change to

=IF(VLOOKUP(X2,Y2:Z10,2,0)="","",VLOOKUP(X2,Y2:Z10,2,0))

this should eliminate the zero.

then for your concatenation use

=TRIM(A1&" "&A2&" "&A3

 

[Chuck]

Hello,
I have an Excel 2003 worksheet where cell
A1 is First Name
A2 is MI ---- When they don't have a middle initial this cell is left blank.
A3 is Last Name.

However I have put a concatenate formula in another cell and whenever the
"A2" is blank it provides a "0" instead of a blank space.
A1 = Richard
A2 = T.
A3 = Jones would concatenate as Richard T. Jones

However without a middle initial I get Richard 0 Jones.
Also when there is a blank I would like to make sure it eliminates the space
and simply shows Richard Jones. But the 0 problem is the biggest. This
worksheet is used as a database for a WORD merged document.

I've posted this before but only got one answer that wasn't appropriate, so
please if anyone has an answer I really need it

Thank you
BOB

=IF(ISBLANK(A2),A1&" "&A3,A1&" "&A2&" "&A3)

Chuck
--

 

[Chuck]

Hello,
I have an Excel 2003 worksheet where cell
A1 is First Name
A2 is MI ---- When they don't have a middle initial this cell is left blank.
A3 is Last Name.

However I have put a concatenate formula in another cell and whenever the
"A2" is blank it provides a "0" instead of a blank space.
A1 = Richard
A2 = T.
A3 = Jones would concatenate as Richard T. Jones

However without a middle initial I get Richard 0 Jones.
Also when there is a blank I would like to make sure it eliminates the space
and simply shows Richard Jones. But the 0 problem is the biggest. This
worksheet is used as a database for a WORD merged document.

I've posted this before but only got one answer that wasn't appropriate, so
please if anyone has an answer I really need it

Thank you
BOB

correction:
=IF(ISBLANK(A2),A1&" "&A3,A1&" "&{referencde cell}&" "&A3)

Chuck
--

 

[Joerg]

Additional that I forgot.
The A2 has a formula that refers to another cell on another worksheet. So it
refers to worksheet 2 cell a2 and this would be the one that's blank. I
think excel is picking up the formula as not being blank and reporting the
zero, but I need it to report blank if there's nothing in the original cell.
Thanks

Change the formula in A2:
=T(reference)

The T function results in an empty string, not 0, if the reference cell is
blank.

Cheers,

Joerg

 

[alexyourfriend]

1. Click the File menu and then choose Options (under Help). In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Choose Advanced in the left pane. In Excel 2003, click the View tab.
3. In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
4. Click OK.