| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT Worksheet function
=IF(LEFT(G7,2) = 17,"Dep", "Amort")
I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
How about =IF(LEFT(G7,2)="17","Dep","Amort")
Dave -- Brevity is the soul of wit. "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
Try:
=IF(VALUE(LEFT(G7,2) )= 17,"Dep", "Amort") if G7 is text =IF(LEFT(G7,2) = "17","Dep", "Amort") HTH "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
If G7 is text or numbers, putting 17 in quotation marks will return the right
answer. So, all that is neede is =IF(LEFT(G7,2)="17","Dep","Amort") From what I can tell, LEFT treats any string of characters as text, even if they are formatted differently than text. Dave -- Brevity is the soul of wit. "Toppers" wrote: Try: =IF(VALUE(LEFT(G7,2) )= 17,"Dep", "Amort") if G7 is text =IF(LEFT(G7,2) = "17","Dep", "Amort") HTH "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
Try:
=IF(LEFT(G7,2) = "17","Dep", "Amort") or =IF(--LEFT(G7,2) = 17,"Dep", "Amort") Regards Trevor "loren.pottinger" wrote in message ps.com... =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
The function LEFT expects text data. Its argument (which I suspect is a
number) should therefore be first converted to text. Also the quantity it is compared to (namely 17) should be presented as text, that is "17". Try this: =IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort") "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
Well, the --LEFT(G7,2) tip is really clever !
"Trevor Shuttleworth" wrote: Try: =IF(LEFT(G7,2) = "17","Dep", "Amort") or =IF(--LEFT(G7,2) = 17,"Dep", "Amort") Regards Trevor "loren.pottinger" wrote in message ps.com... =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
All,
I am baffled none of those work. They only return dep or Amort for all the cells regardless of what is in column G. Bernard wrote: The function LEFT expects text data. Its argument (which I suspect is a number) should therefore be first converted to text. Also the quantity it is compared to (namely 17) should be presented as text, that is "17". Try this: =IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort") "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
The formula I give you above worked for me.
What happens if you use the formula I give you and then you press F9 (which forces Excel to recalculate)? Any luck? -- Brevity is the soul of wit. "loren.pottinger" wrote: All, I am baffled none of those work. They only return dep or Amort for all the cells regardless of what is in column G. Bernard wrote: The function LEFT expects text data. Its argument (which I suspect is a number) should therefore be first converted to text. Also the quantity it is compared to (namely 17) should be presented as text, that is "17". Try this: =IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort") "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
Dave. When it is false, it is not returning Amort
Dave F wrote: The formula I give you above worked for me. What happens if you use the formula I give you and then you press F9 (which forces Excel to recalculate)? Any luck? -- Brevity is the soul of wit. "loren.pottinger" wrote: All, I am baffled none of those work. They only return dep or Amort for all the cells regardless of what is in column G. Bernard wrote: The function LEFT expects text data. Its argument (which I suspect is a number) should therefore be first converted to text. Also the quantity it is compared to (namely 17) should be presented as text, that is "17". Try this: =IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort") "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
Oooops. My bad it is working. Thank you.
Dave F wrote: The formula I give you above worked for me. What happens if you use the formula I give you and then you press F9 (which forces Excel to recalculate)? Any luck? -- Brevity is the soul of wit. "loren.pottinger" wrote: All, I am baffled none of those work. They only return dep or Amort for all the cells regardless of what is in column G. Bernard wrote: The function LEFT expects text data. Its argument (which I suspect is a number) should therefore be first converted to text. Also the quantity it is compared to (namely 17) should be presented as text, that is "17". Try this: =IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort") "loren.pottinger" wrote: =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
LEFT Worksheet function
I learned it here ;-)
"Bernard" wrote in message ... Well, the --LEFT(G7,2) tip is really clever ! "Trevor Shuttleworth" wrote: Try: =IF(LEFT(G7,2) = "17","Dep", "Amort") or =IF(--LEFT(G7,2) = 17,"Dep", "Amort") Regards Trevor "loren.pottinger" wrote in message ps.com... =IF(LEFT(G7,2) = 17,"Dep", "Amort") I constructed the above function to return Dep if the first two digits starting from the leftmost digit in cell G7 is 17 and return Amort otherwise, please help. This formula looks so right to me. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
| Evaluating Today() function only once in a worksheet | Excel Worksheet Functions | |||
| Offset function referencing worksheet | Excel Discussion (Misc queries) | |||
| changing type style and colour within a worksheet function | Excel Worksheet Functions | |||
| Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Linear Mode
