A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Why does this work?



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 09:38 PM posted to microsoft.public.excel.misc
M Thompson
external usenet poster
 
Posts: 38
Default 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
  #2  
Old May 16th, 2009, 09:54 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #3  
Old May 16th, 2009, 10:53 PM posted to microsoft.public.excel.misc
Ken Wright
external usenet poster
 
Posts: 199
Default 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



  #4  
Old May 17th, 2009, 05:31 AM posted to microsoft.public.excel.misc
M Thompson
external usenet poster
 
Posts: 38
Default 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

  #5  
Old May 17th, 2009, 05:33 AM posted to microsoft.public.excel.misc
M Thompson
external usenet poster
 
Posts: 38
Default 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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.