how to combine including blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karmen - 14 Mar 2006 22:17 GMT

Hello,
I am trying to combine data from various cells into one cell.....using
a formula such as =c9&d9&e9 however if one of the cells were blank I
get a 0 in the end combination. What can I do to eliminate the zero?

Or pehaps there is a better way to do this. I have text and numbers in
15 rows / 4 columns. I would like to enter into a separate cell the
combined information from row a1:d1 then a2:d2 etc.

Unfortunately today, I can not seem to think of a better solution. any
suggestions?

Signature

Karmen

Reply to this Message

Bernard Liengme - 14 Mar 2006 22:28 GMT

I cannot reproduce this. If I have a blank in B1 then A1&B1&C1 gives the
same as A1&C1
Tell us more

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hello,
> I am trying to combine data from various cells into one cell.....using
[quoted text clipped - 7 lines]
> Unfortunately today, I can not seem to think of a better solution. any
> suggestions?

Reply to this Message

kevindmorgan - 14 Mar 2006 23:33 GMT

=CONCATENATE(A1,B1,C1)

I also couldn't reproduce your results, but this should work.

Karmen Wrote:

Signature

kevindmorgan

Reply to this Message

Dave Peterson - 14 Mar 2006 23:46 GMT

Any chance that one of the cells actually contains a 0, but is hidden by
formatting--either a custom format or format|conditional formatting or just
font/fill color?

If you select each cell, what do you see in the formula bar?

> Hello,
> I am trying to combine data from various cells into one cell.....using
[quoted text clipped - 13 lines]
> Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972
> View this thread: http://www.excelforum.com/showthread.php?threadid=522413

Signature

Dave Peterson

Reply to this Message

David McRitchie - 15 Mar 2006 05:04 GMT

You will see that if you refer to an empty cell on another sheet
="abc" & sheet2!e444

=if(ISBLANK(shee4!c9),"",sheet4!c9) & ...

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Any chance that one of the cells actually contains a 0, but is hidden by
> formatting--either a custom format or format|conditional formatting or just
[quoted text clipped - 19 lines]
> > Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972
> > View this thread: http://www.excelforum.com/showthread.php?threadid=522413

Reply to this Message

Karmen - 15 Mar 2006 17:02 GMT

Sorry, after reading the responses I realized that the sheet I am tryin
to combine information on is an automatic fill data sheet which add
zeros for blank cells from the master sheet....and I had supressed th
zeros showing on the form...I had completely forgot the zero i
actually in the cell, just not showing. with that added information
is there a way i can combine the cell data and not have the zero show

Reply to this Message

Dave Peterson - 15 Mar 2006 17:31 GMT

Something like:

=if(a1=0,"",a1)&if(b1=0,"",b1)&....

might work.

> Sorry, after reading the responses I realized that the sheet I am trying
> to combine information on is an automatic fill data sheet which adds
[quoted text clipped - 8 lines]
> Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972
> View this thread: http://www.excelforum.com/showthread.php?threadid=522413

Signature

Dave Peterson

Reply to this Message

Karmen - 15 Mar 2006 19:14 GMT

Thank you very much !!!

Dave Peterson Wrote:

> Something like:
>
[quoted text clipped - 25 lines]
>
> Dave Peterso

Reply to this Message