Excel: Return Error Statement in Text Formula

Hi,

A1 = #Value!
A2 = Test

I would like A3 to display: "#Value! Test"

I enter the formula: "=A1&" "&A2" however it returns just the value error. How can I get the formula to return the text and the error?

Cheers,
LVL 1
dabug80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try if you only want the value error to show

=IF(ISERROR(ERROR.TYPE(A1)=3);A1;"#VALUE!")&" "&a2

if not maybe better a User Defined Function like

Function fShowError(rng As Range) As Variant

Set R = rng.Resize(1, 1)
If IsError(R.Value) = True Then
    If R.Value = CVErr(xlErrValue) Then
        Res = "#VALUE!"
    Else
        Res = "Another error"
    End If
Else
    Res = R.Value
End If
fShowError = Res

End Function

Regards
0
helpfinderIT ConsultantCommented:
in A3 use
=the same formula as you have in A1&" "&A2
0
Rgonzo1971Commented:
my New Version

=IFERROR(CHOOSE(ERROR.TYPE(A3),"#NULL!","#DIV/0!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"),A1)&" "&A2
Regards
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

dabug80Author Commented:
Hi Rgonzo,

Sady that didn't work
0
Rgonzo1971Commented:
See example
EDIT Corrected Formula

=IFERROR(CHOOSE(ERROR.TYPE(A1),"#NULL!","#DIV/0!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"),A1)&" "&A2
EE20140910.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
As soon as you refer to a cell whose calculated result is an error, the result of the reference to that cell will also be the same error.

Therefore, the following should work:

=IFERROR(A1,"#Value! "&A2)

Extending the question, what do you want in A3 if A1 is not an error?

Thanks,
Rob H
0
Rob HensonFinance AnalystCommented:
Ahhh, just re-read the solution from RGonzo and I realise now he has forced the behaviour of the statement in my previous comment, (refer to error = same error) and pulled in the same text value as the error result. Nice one!!

If you want text the same as the error result then that is the way to go. If you want "#Value!" for all errors then mine will suffice.

Thanks
Rob H
0
dabug80Author Commented:
Excellent. Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.