checking for a particular string in a cell

[Christopher Naveen]

Hi,

I have a col which contains lot of customer names (Col A). Now I want to
give a formula in col B to check for the few characters in col A and classify.

Ex :

Col A Col B
ABC Stores Stores (I am trying to give formual in Col
B to check the Character
"Stores" in Col A. A1 contains "ABC
Stores", in this cell i
need to check
whether "stores" is
available or not if available
classify as "Stores" in
Col B.)
DEF Mal
GHI complex
JKL Stores Stores

I request anybody to help me with a solution.

-Chr

 

[macropod]

Hi Christopher,

You post's layout makes it very difficult to work out what you're trying to do. However, if you want to find the string 'Stores' in
a cell (eg Cell A1), you could use:
=FIND("Stores",A1)
This will return the position of the first character in the string where 'Stores' appears, or #N/A! if it isn't found.
You can then turn that into a TRUE/FALSE result via:
=NOT(ISERROR(FIND("Stores",A1)))
So, if you just want 'Stores' in ColB:
=IF(NOT(ISERROR(FIND("Stores",A1))),"Stores","")
Alternatively:
=IF(ISERROR(FIND("Stores",A1)),"","Stores")

HTH

Cheers

 

[Christopher Naveen]

Hi,

Thank u very much. I am extremely sorry while typing the window was small
and i cant able to type it propoerly. You gave me the exact solution for what
i requested. Thanks once again.

I am again facing the problem because FIND will search the strings and i
think it is case sensitive. I want to search for the character "ABC Stores"
in a cell and it may be caps or small it shud return a value.

Pls. tell me the function which is not case sensitive.

Thank u very much

Waiting for ur reply.

chris

Hi Christopher,

You post's layout makes it very difficult to work out what you're trying to do. However, if you want to find the string 'Stores' in
a cell (eg Cell A1), you could use:
=FIND("Stores",A1)
This will return the position of the first character in the string where 'Stores' appears, or #N/A! if it isn't found.
You can then turn that into a TRUE/FALSE result via:
=NOT(ISERROR(FIND("Stores",A1)))
So, if you just want 'Stores' in ColB:
=IF(NOT(ISERROR(FIND("Stores",A1))),"Stores","")
Alternatively:
=IF(ISERROR(FIND("Stores",A1)),"","Stores")

HTH

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hi,

I have a col which contains lot of customer names (Col A). Now I want to
give a formula in col B to check for the few characters in col A and classify.

Ex :

Col A Col B
ABC Stores Stores (I am trying to give formual in Col
B to check the Character
"Stores" in Col A. A1 contains "ABC
Stores", in this cell i
need to check
whether "stores" is
available or not if available
classify as "Stores" in
Col B.)
DEF Mal
GHI complex
JKL Stores Stores

I request anybody to help me with a solution.

-Chr

 

[Pete_UK]

Use the function SEARCH instead of FIND.

Hope this helps.

Pete