CONCATENATE IF macro or function?

F

FJ

Hi, I’m using the following formula to pull data from several worksheets onto
another worksheet:
=CONCATENATE("Product 1: ",Sheet1!A1,"
","Product 2: ",Sheet2!A1,"
","Product 3: ",Sheet3!A1)

This gives me the following result, assuming that the data in A1 on Sheets
1, 2, and 3 was Apples, Oranges, and Pears, respectively:
Product 1: Apples
Product 2: Oranges
Product 3: Pears

This works fine. The only problem is if there were no data in, say, cell A1
on Sheet3. Then my results would look like this:
Product 1: Apples
Product 2: Oranges
Product 3:

I would like to use a formula or a macro that would allow me to do a sort of
“concatenate if†type of thing, so that if a cell is blank, it would not
concatenate the “Product†part. I’m not sure if this can be done with a
regular Excel formula. I thinking it might need a user defined function or a
macro, if it’s possible at all. Can anyone help? Thanks in advance for any
information.
 
M

Mike H

Hi,

It's a bit messy but here's a formula to do it.

=IF(Sheet1!A1<>"","Product 1: "& Sheet1!A1&" ","")&
IF(Sheet2!A1<>"","Product 2: "& Sheet2!A1&" ","")&IF(Sheet3!A1<>"","Product
3: "& Sheet3!A1,"")

Mike
 
F

FJ

Hi, Mike, thank you so much! :) Your formula worked great! :) I didn't
think this could be done with a regular formula. I always learn so much from
these discussion groups. :)

Thanks again! :)
 
M

Mike H

Glad I could help

FJ said:
Hi, Mike, thank you so much! :) Your formula worked great! :) I didn't
think this could be done with a regular formula. I always learn so much from
these discussion groups. :)

Thanks again! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top