Why does this work?
 

I got this formula from Max, Singapo =VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
I would like to know how the +0 is working in this. I've spent about alot of
time in Help and on the internet trying to find out, but I think you guys are
the ones that can clear it up. I know it's making the formala recognize the
concatenation as a number, but how?
Thanks and have...
--
OneFineDay

Why does this work?
 

=A1&B1
concatenates two strings.

So if A1 contained 1 and B1 contained 23, the
=A1&B1
would result in the string 123

By adding 0 to the string, excel will coerce the string value to a real number
123.

If you put
=A1&B1
in C1
and
=(A1&B1)+0
in D1

You can see the difference with formulas like:
=isnumber(c1)
=isnumber(d1)
and
=istext(c1)
=istext(d1)


M Thompson wrote:

I got this formula from Max, Singapo =VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
I would like to know how the +0 is working in this. I've spent about alot of
time in Help and on the internet trying to find out, but I think you guys are
the ones that can clear it up. I know it's making the formala recognize the
concatenation as a number, but how?
Thanks and have...
--
OneFineDay

--

Dave Peterson

Why does this work?
 

Just to add to Dave's reply, any mathematical operation on a piece of text
that can be interpreted as numeric, will coerce it to a numeric.

Same answer can be obtained with:

=VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
=VLOOKUP((A1&B1)*1,Sheet2!A1:C6,3,0)
=VLOOKUP(--(A1&B1),Sheet2!A1:C6,3,0)

Regards
Ken..................



"M Thompson" wrote in message
...
I got this formula from Max, Singapo
=VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
I would like to know how the +0 is working in this. I've spent about alot
of
time in Help and on the internet trying to find out, but I think you guys
are
the ones that can clear it up. I know it's making the formala recognize
the
concatenation as a number, but how?
Thanks and have...
--
OneFineDay

Why does this work?
 

Hi Dave
Thanks for the explanation. It will be a definite help for the future.
P.S. This is the second try at replying to your answer. I keep getting a
temporarily out of service notice, so let's hope you get this one!

Have..
--
OneFineDay


"Dave Peterson" wrote:

=A1&B1
concatenates two strings.

So if A1 contained 1 and B1 contained 23, the
=A1&B1
would result in the string 123

By adding 0 to the string, excel will coerce the string value to a real number
123.

If you put
=A1&B1
in C1
and
=(A1&B1)+0
in D1

You can see the difference with formulas like:
=isnumber(c1)
=isnumber(d1)
and
=istext(c1)
=istext(d1)


M Thompson wrote:

I got this formula from Max, Singapo =VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
I would like to know how the +0 is working in this. I've spent about alot of
time in Help and on the internet trying to find out, but I think you guys are
the ones that can clear it up. I know it's making the formala recognize the
concatenation as a number, but how?
Thanks and have...
--
OneFineDay

--

Dave Peterson

Why does this work?
 

Ken-Thanks for adding to the info. Every big bit helps!

Have..
--
OneFineDay


"Ken Wright" wrote:

Just to add to Dave's reply, any mathematical operation on a piece of text
that can be interpreted as numeric, will coerce it to a numeric.

Same answer can be obtained with:

=VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
=VLOOKUP((A1&B1)*1,Sheet2!A1:C6,3,0)
=VLOOKUP(--(A1&B1),Sheet2!A1:C6,3,0)

Regards
Ken..................



"M Thompson" wrote in message
...
I got this formula from Max, Singapo
=VLOOKUP((A1&B1)+0,Sheet2!A1:C6,3,0)
I would like to know how the +0 is working in this. I've spent about alot
of
time in Help and on the internet trying to find out, but I think you guys
are
the ones that can clear it up. I know it's making the formala recognize
the
concatenation as a number, but how?
Thanks and have...
--
OneFineDay