formula to concatenate cells with data

Hi,

Is there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of

Please Login or Register  to view this content.

I know I can do this with VBA was looking for a non VBA solution

Do you mean something along the lines of:

Please Login or Register  to view this content.

You will need to add a function to your sheet to allow "string" concatenation, concatenation based on the results of a test.

Let's add the function to your sheet first.
Press Alt-F11 to open the VBEditor
Click Insert>Module
Paste the following code into the window

Please Login or Register  to view this content.

(Source)
The Alt-F11 to close the editor and save your sheet.

The format for normal simple use is:

=StringConcat(",",A1:E1)

But it will allow you to insert a "criteria test" and activate it as an array, like so:
=StringConcat(",",IF(A1:E1<>"",A1:E1,""),"")
...confirmed with CTRL-SHIFT-ENTER.

When you use the second version, it skips the ones that don't match the test and concatenates the others with the delimiter you specified in the formula.