Concatenate a range but ignore any cell with 0.

[slimjimbo21]

Hi there,

I'm concatenating (is that a word?!) a range of data into one cell using the =CONCATENATE function.

However, I want the function to ignore and skip any cell which has a '0' in.

for example, data:

A1= 7
A2= 0
A3= 9

A5 =CONCATENATE(A1,A2,A3)

I want to see A5= 79, but have A5= 709

 

[Michael M]

try

=A1&A2&A3

 

[ChrisOswald]

Try:

=IF(A1<>0,A1,"")&IF(A2<>0,A2,"")&IF(A3<>0,A3,"")

 

[Michael M]

Delete post.....having a Seniors moment

 

[Special-K99]

Rather than check for zeroes, why not concatenate everything and then substitute all 0's with nulls ("") ?

 

[jasonb75]

Array formula, requires the morefunc add-in

=MCONCAT(IF(A1:A3<>0,A1:A3,""))

 

[Peter_SSs]

Rather than check for zeroes, why not concatenate everything and then substitute all 0's with nulls ("") ?

Provided only that none of the cells contain numbers longer than 1 digit! See cell B5 below.

A more robust formula is in A14. Both formulas copied across.

Of course Chris has posted a fine solution already. My suggestion below may become more useful if there are more cells involved.

Excel Workbook
	A	B
1	7	0
2	0	101
3	9	9
4
5	79	119
6
7
8	7	0
9	0	101
10	9	9
11
12	79	1019
13
Ignore 0 cells

 

[slimjimbo21]

Brilliant help, thanks very much all.

As it ended up, the simplest option worked! I replaced the 0 with ="", as recommended.

Thanks very much for quick responses.

sj

 

[claidheamdanns]

Can I reawaken this sleeping thread?

I'm having a similar problem. I am doing a data merge where I am bringing individual spreadsheets for calendar months into one master spreadsheet that contains everything, including previous Dec and upcoming January, Gregorian dates, moon phases — the whole enchilada.

When I concatenate, cells from the individual spreadsheets which have no content in them at all come into the master spreadsheet with a "0."

I'm hoping there is a formula to help with this, without having to do a find and replace for zeros.

 

[claidheamdanns]

Hey, one of my coworkers walked in while I was working on this, and they gave me the solution. Here's an example of my January 1st cell:

=IF(January!A1=0,"",January!A1)

Hopefully, this will help someone else out, since this is working perfectly. On the master spreadsheet, I am ending up with one massive data merge (exported to CSV) which can be imported into multiple InDesign files, all with different formatting, and update them all at once each year, instead of hand keying all the dates in each one. It's saving MASSIVE amounts of time! w00t!