Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
Hey,
A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
=IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1))
This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
Thanks Luke for your help.
Unfortunately it does not work completely. It looks like the formula FIND only searches for the entire text and not a section of text, therefore it won't have any results if you search for a section of the text (in this case , ). Is there something to get anything like a contain search order? -- Kees Vos "Luke M" wrote: =IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1)) This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
I'm not sure I understand. The FIND command is only looking for the comma.
Nothing else. It then returns a number(place number). The LEFT commands takes all the digits up to that number, minus 1(to exclude the comma) Could you post an example of what you are searching, and what is returned? -- Best Regards, Luke M "Kees Vos" wrote: Thanks Luke for your help. Unfortunately it does not work completely. It looks like the formula FIND only searches for the entire text and not a section of text, therefore it won't have any results if you search for a section of the text (in this case , ). Is there something to get anything like a contain search order? -- Kees Vos "Luke M" wrote: =IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1)) This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
I have to translate the formulas since i work in a Dutch version. But I think
the problem shows when i use this formula: FIND(",";A1) A1 has the text 14050001 EH, Filmkastpet Hvide RAL 1028 But the result of the formula is: #N/B If the formula is: FIND("14050001 EH, Filmkastpet Hvide RAL 1028";A1) The result is 14050001 EH, Filmkastpet Hvide RAL 1028, so thats good. My conclusion is that the formula FIND only searches for the enitre content. Hope this is clear. Thanks anyway! -- Kees Vos "Luke M" wrote: I'm not sure I understand. The FIND command is only looking for the comma. Nothing else. It then returns a number(place number). The LEFT commands takes all the digits up to that number, minus 1(to exclude the comma) Could you post an example of what you are searching, and what is returned? -- Best Regards, Luke M "Kees Vos" wrote: Thanks Luke for your help. Unfortunately it does not work completely. It looks like the formula FIND only searches for the entire text and not a section of text, therefore it won't have any results if you search for a section of the text (in this case , ). Is there something to get anything like a contain search order? -- Kees Vos "Luke M" wrote: =IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1)) This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
Aaaah, I changed my language of Excel to English (wonderfully all the
formula's are translated! :D) and found out that I used LOOKUP in stead of FIND. Changed it to FIND and now it works! Superr! Thanks a lot! -- Kees Vos "Luke M" wrote: I'm not sure I understand. The FIND command is only looking for the comma. Nothing else. It then returns a number(place number). The LEFT commands takes all the digits up to that number, minus 1(to exclude the comma) Could you post an example of what you are searching, and what is returned? -- Best Regards, Luke M "Kees Vos" wrote: Thanks Luke for your help. Unfortunately it does not work completely. It looks like the formula FIND only searches for the entire text and not a section of text, therefore it won't have any results if you search for a section of the text (in this case , ). Is there something to get anything like a contain search order? -- Kees Vos "Luke M" wrote: =IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1)) This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get section of text from cell
Hurrah! Glad we could solve it. Thanks for feedback.
-- Best Regards, Luke M "Kees Vos" wrote: Aaaah, I changed my language of Excel to English (wonderfully all the formula's are translated! :D) and found out that I used LOOKUP in stead of FIND. Changed it to FIND and now it works! Superr! Thanks a lot! -- Kees Vos "Luke M" wrote: I'm not sure I understand. The FIND command is only looking for the comma. Nothing else. It then returns a number(place number). The LEFT commands takes all the digits up to that number, minus 1(to exclude the comma) Could you post an example of what you are searching, and what is returned? -- Best Regards, Luke M "Kees Vos" wrote: Thanks Luke for your help. Unfortunately it does not work completely. It looks like the formula FIND only searches for the entire text and not a section of text, therefore it won't have any results if you search for a section of the text (in this case , ). Is there something to get anything like a contain search order? -- Kees Vos "Luke M" wrote: =IF(ISERROR(LEFT(a1,FIND(",",a1)-1)),"",LEFT(a1,FIND(",",a1)-1)) This will find the first comma and return everything in front of it. If there is an error (comma not found, or is 1st digit, cell will remain blank) -- Best Regards, Luke M "Kees Vos" wrote: Hey, A long row of cells contains a item number and a item name, something like this: 123456, item name I only need the item number as a result. Is there a way to extract only the text in front of a seperation mark (in this case the , )? Note, the item number can contain numbers and/or letters and a interspace. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrap text in header section Excel 2007 | Excel Discussion (Misc queries) | |||
how do I highlight section in cell | Excel Discussion (Misc queries) | |||
How do I print just the section a through f only? | New Users to Excel | |||
Converting selected section of text to numbers | Excel Discussion (Misc queries) | |||
Stripping section of text | Excel Worksheet Functions |