Get section of text from cell

[Kees Vos]

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!

 

[Luke M]

=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)

 

[Kees Vos]

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?

 

[Luke M]

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?

 

[Kees Vos]

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]

Aaaah, I changed my language of Excel to English (wonderfully all the
formula's are translated! ) and found out that I used LOOKUP in stead of
FIND. Changed it to FIND and now it works! Superr!

Thanks a lot!

 

[Luke M]

Hurrah! Glad we could solve it. Thanks for feedback.