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

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

"rjr" wrote in message
...
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

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56881

On Sun, 6 Aug 2006 17:47:31 -0400, "rjr"
wrote:

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
--

On Sun, 6 Aug 2006 17:47:31 -0400, "rjr"
wrote:

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
--

"rjr" wrote in message
...
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