Concatenate a range but ignore any cell with 0.

slimjimbo21

New Member
Joined
Aug 19, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
try
Code:
=A1&A2&A3
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Delete post.....having a Seniors moment
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
Array formula, requires the morefunc add-in

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
AB
170
20101
399
4
579119
6
7
870
90101
1099
11
12791019
13
Ignore 0 cells
 
Last edited:

slimjimbo21

New Member
Joined
Aug 19, 2010
Messages
2
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

New Member
Joined
Sep 6, 2016
Messages
5
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

New Member
Joined
Sep 6, 2016
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top