Formula to Find a specific text within a text string

[ewong]

Hi,

I need a formula to find a specific text within a text string.

e.g.:

"Sales Account Representative Europe" in cell A1

I want to build a formula in cell A2 that would return "Sales" in that text string above

Thanks

[Jakobshavn]

=left(a1,5)

[ewong]

the "sales" can be anywhere in the text string. it could be in the middle, first, or last...

[Jakobshavn]

O.K. then.

If A1 contains the full string and B1 contains "Sales"

then the formula:

=FIND(B1,A1) will tell you where in A1, B1 can be found. For example if A1 contains:
hfshfjssalesvcjvh
the formula will return 8

Is this what you need???

[ewong]

no, i dont need "8". i need "Sales"

[oeldere]

in addition on the reaction of Jacobshavn in #4.

=if(FIND(B1,A1)>0,"sales","there is no sales in this cell")

[Ace_XL]

perhaps like this..

=IF(ISNUMBER(SEARCH("Sales",A1)),"Sales","")

[Jakobshavn]

Then use:

=IF(ISERROR(FIND(B1,A1)),"",B1) with the big string in A1 and "Sales" in B1

If "Sales" is found in A1, then "sales" is displayed.
If "Sales is not found in A1, then nothing is displayed.