Concatenate returns #value error if blank cells are included in the range

E

Ernie Sersen

I am trying to combine 4 columns of data into 1 column
using =concatenate(A1,B1,C1,D1). It returns a #value
error if any of the cells A1-D1 are blank. I tried
copy/paste special to convert all of the cells to 'value'
with no affect. Please help.
 
F

Frank Kabel

Hi
this formula should not return a #VALUE error. Are you sure A1:D1 do
not contain this error?
 
G

Gary Thomson

Hello,

I can't think of anything obvious that you may be doing
wrong here.

However, try setting each of A1, B1, C1, D1 to "general"
number format, as well as applying this setting to the
cell you are trying to put your concatenated value in.

Also, did you know you can shorten this formula to:

=A1&B1&C1&D1

the "&" sign simply replaces the "CONCATENATE" string.

Happy to Help,

Gary Thomson
 
G

Guest

I used the TEXT command TEXT(A1,"#")&TEXT(B1,"#")&TEXT
(C1,"#")&TEXT(D1,"#") to solve this one. It must have
been a formatting interpretation of the blank cells that
caused the problem.

THANKS...
 

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