Constant in concatenation

[Dave]

Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

 

[Dave Peterson]

Maybe you can check to see if any of the cells are used:
=if(counta(a1:e1)=0,"","C" & ...)

or if all the cells are filled:
=if(counta(a1:e1)<5,"","C" & ...)

 

[Dave]

Hi,
Not sure if I'm on the right track, but if you are concatenating Cells A1,
B1, C1, D1, all into E1, perhape something like:
IF(COUNTA(A11)=4,CONCATENATE(A11),"")
or some other use of COUNTA to check that all contributing cells have data.
Regards - Dave.

 

[T. Valko]

It would have helped if you would've posted your formula.

So, my best guess is:

=IF(COUNTA(A11)<4,"","C"&A1&B1&C1&D1)

 

[T. Valko]

....CONCATENATE(A11)

That won't work. CONCATENATE won't work on arrays. You'd have to do this:

.....CONCATENATE(A1,B1,C1,D1)

Saves a few keystrokes if you do it like this:

.....A1&B1&C1&D1

 

[Spiky]

Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

Just for fun:
=IF(OR(ISBLANK(E27:E30)),"","C"&E27&E28&E29&E30)
Use CTRL-SHIFT-ENTER since it is an array.