| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a simple solution?
Hello
I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a simple solution?
Your problem may be that the results from your LEFT() function are text and
you're looking in a series of numbers. Try this: =VLookUp(Value(Left(mycell,6),lookuprange,index)) HTH, Elkar "Bobby" wrote: Hello I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a simple solution?
If you mean just return the data before the first comma then try:
=LEFT(G4,FIND(",",G4,1)-1) This will be as text, if you want it to be a number then use: =--LEFT(G4,FIND(",",G4,1)-1) -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Hello I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a simple solution?
Sandy
Thanks for the reply This looks like what I am looking for. My cell A1 = 70592525, 70592527 Formatted as number However your formula returns #value What am I doing wrong? Thanks again! "Sandy Mann" wrote: If you mean just return the data before the first comma then try: =LEFT(G4,FIND(",",G4,1)-1) This will be as text, if you want it to be a number then use: =--LEFT(G4,FIND(",",G4,1)-1) -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Hello I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a simple solution?
Thanks
Im not sure what I changed but it is working now. One followup question (information I forgot to provide in OP) The cell could be only one number. What is the best way to handle this? I was thinknig maybe using LEN8. Thanks again "Sandy Mann" wrote: The $2 answer is I don't know. If I format the cell as Number and enter 70592525,70592527 it displays as: 7,059,252,570,592,527 and gives the #Value! error as you say. However, if I include the space after the comma it turns the entry into text and the formula works even although the cell is still formatted as Number. Try entering in anoth cell =ISTEXT(A1) and see if you get TRUE (if the entry is really Text) or FALSE. If you still can't get it to work then you can send me a sample sheet privately -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Sandy Thanks for the reply This looks like what I am looking for. My cell A1 = 70592525, 70592527 Formatted as number However your formula returns #value What am I doing wrong? Thanks again! "Sandy Mann" wrote: If you mean just return the data before the first comma then try: =LEFT(G4,FIND(",",G4,1)-1) This will be as text, if you want it to be a number then use: =--LEFT(G4,FIND(",",G4,1)-1) -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Hello I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a simple solution?
If I understand correctly try:
=IF(ISERROR(LEFT(A1,FIND(",",A1,1)-1)),A1,--LEFT(A1,FIND(",",A1,1)-1)) -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Thanks Im not sure what I changed but it is working now. One followup question (information I forgot to provide in OP) The cell could be only one number. What is the best way to handle this? I was thinknig maybe using LEN8. Thanks again "Sandy Mann" wrote: The $2 answer is I don't know. If I format the cell as Number and enter 70592525,70592527 it displays as: 7,059,252,570,592,527 and gives the #Value! error as you say. However, if I include the space after the comma it turns the entry into text and the formula works even although the cell is still formatted as Number. Try entering in anoth cell =ISTEXT(A1) and see if you get TRUE (if the entry is really Text) or FALSE. If you still can't get it to work then you can send me a sample sheet privately -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Sandy Thanks for the reply This looks like what I am looking for. My cell A1 = 70592525, 70592527 Formatted as number However your formula returns #value What am I doing wrong? Thanks again! "Sandy Mann" wrote: If you mean just return the data before the first comma then try: =LEFT(G4,FIND(",",G4,1)-1) This will be as text, if you want it to be a number then use: =--LEFT(G4,FIND(",",G4,1)-1) -- HTH Sandy with @tiscali.co.uk "Bobby" wrote in message ... Hello I am having trouble with a lookup formula. The lookup value is in a cell with several other comma delimited values, ie., 123456, 12345678, 123452. The value I want to use is the portion to the left of the first comma. The numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there a way to do this without doing a text to columns? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros in Excel(probably a simple solution) | Excel Discussion (Misc queries) | |||
| Show trial solution | Excel Worksheet Functions | |||
| Surely a simple solution exists | Excel Worksheet Functions | |||
| Help with what should be a simple formula | Excel Worksheet Functions | |||
| Generating excel combinations | Excel Discussion (Misc queries) | |||
Hybrid Mode
