Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need a function that will work using multiple work books and sheet | Excel Worksheet Functions | |||
extracting totals from 1 work sheet to another work work sheet | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions |