ExcelBanter - concatenate #N/A

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - concatenate #N/A (https://www.excelbanter.com/excel-discussion-misc-queries/115800-concatenate-n.html)

concatenate #N/A

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

Answer: concatenate #N/A

Hi there!

I understand that you are having trouble concatenating cells that contain the #N/A error. This can be frustrating, but there is a workaround that you can use to achieve your desired result.

One way to do this is by using the IFERROR function in your concatenate formula. The IFERROR function allows you to specify a value to be returned if a formula results in an error. In this case, you can use it to return an empty string ("") if the cell contains the #N/A error.

Here's an example formula:

=CONCATENATE(IFERROR(A1,""),IFERROR(B1,""),IFERROR (C1,""))

In this formula, A1, B1, and C1 are the cells that you want to concatenate. The IFERROR function is used to check each cell for the #N/A error. If the cell contains the #N/A error, an empty string is returned instead. The CONCATENATE function then combines the values of the cells that do not contain the #N/A error.

concatenate #N/A

What errors are you seeing? Post the specific formula you are using and any
errors which are returned.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

concatenate #N/A

Hi Dave, thanks for your reply

The only formula that i have a problem with is the concatenate one. i will
try and example.

my command =CONCATENATE(A1,B1,C1)

A1 = "15"
B1 = "SAUSAGES"
C1 = "INAPAN"

this results 15SAUSAGESINAPAN

A1 = "15"
B1 = "SAUSAGES"
C1 = "#N/A"

this results #N/A

what i am trying to achieve is the result 15SAUSAGES#N/A

I hope this makes more sense,

thanks
Bry

"Dave F" wrote:

What errors are you seeing? Post the specific formula you are using and any
errors which are returned.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

concatenate #N/A

OK, try this: =CONCATENATE(A1,B1,IF(ISERROR(C1),"#N/A",C1))

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi Dave, thanks for your reply

The only formula that i have a problem with is the concatenate one. i will
try and example.

my command =CONCATENATE(A1,B1,C1)

A1 = "15"
B1 = "SAUSAGES"
C1 = "INAPAN"

this results 15SAUSAGESINAPAN

A1 = "15"
B1 = "SAUSAGES"
C1 = "#N/A"

this results #N/A

what i am trying to achieve is the result 15SAUSAGES#N/A

I hope this makes more sense,

thanks
Bry

"Dave F" wrote:

What errors are you seeing? Post the specific formula you are using and any
errors which are returned.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

concatenate #N/A

Thanks Dave,

that worked - probably a good time to mention om concatenating 30 cells, 28
of which could possible have "#N/A" as a result.

This is going to be one very long formula.

cheers for your help
Bry

"Dave F" wrote:

OK, try this: =CONCATENATE(A1,B1,IF(ISERROR(C1),"#N/A",C1))

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi Dave, thanks for your reply

The only formula that i have a problem with is the concatenate one. i will
try and example.

my command =CONCATENATE(A1,B1,C1)

A1 = "15"
B1 = "SAUSAGES"
C1 = "INAPAN"

this results 15SAUSAGESINAPAN

A1 = "15"
B1 = "SAUSAGES"
C1 = "#N/A"

this results #N/A

what i am trying to achieve is the result 15SAUSAGES#N/A

I hope this makes more sense,

thanks
Bry

"Dave F" wrote:

What errors are you seeing? Post the specific formula you are using and any
errors which are returned.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

concatenate #N/A

If that's the case you may wish to suppress the #N/A errors or force them to
return a text N/A value.

The problem you were running into resulted from the fact that #N/A is a
logical operator not a text value.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Thanks Dave,

that worked - probably a good time to mention om concatenating 30 cells, 28
of which could possible have "#N/A" as a result.

This is going to be one very long formula.

cheers for your help
Bry

"Dave F" wrote:

OK, try this: =CONCATENATE(A1,B1,IF(ISERROR(C1),"#N/A",C1))

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi Dave, thanks for your reply

The only formula that i have a problem with is the concatenate one. i will
try and example.

my command =CONCATENATE(A1,B1,C1)

A1 = "15"
B1 = "SAUSAGES"
C1 = "INAPAN"

this results 15SAUSAGESINAPAN

A1 = "15"
B1 = "SAUSAGES"
C1 = "#N/A"

this results #N/A

what i am trying to achieve is the result 15SAUSAGES#N/A

I hope this makes more sense,

thanks
Bry

"Dave F" wrote:

What errors are you seeing? Post the specific formula you are using and any
errors which are returned.

Dave
--
Brevity is the soul of wit.

"Bry" wrote:

Hi

I have a problem concatenating cells, all are formated as text but some have
been taken from another worksheet where the cells have come back with a #N/A
result, in instances where this happened then the concatenate also errors.
What im looking for is a way of concatenating everything that is in the
cells selected even if its a '#N/A' error.

I have a question related to this thread. If I have:
A2 = Smith
B2 = Taylor
C2 = Miller

I can use
=CONCATENATE(A2,B2,C2) to get SmithTaylorMiller.

But if I have
A2 = Smith
B2 = #N/A
C2 = Miller
...using the same formula, I just get #N/A.
Is there a way to get the results of A2 and C2, even if B2 has #N/A?
I would like a result like Smith#N/AMiller.
Possible?