How to find an occurrence and repeat it

[RayBox]

Hi All,

I'm looking for some help with a formula. I've got a large database of automotive workshops with the company name in column A. I need a formula that will search column A for the occurrence of the word euro within the company name and than repeat the word euro in column B next to the occurrence. The idea is to find the european type workshops and be able to group them. Thanks in advance for your help.

Regards,
Ray

[Paul]

Hi Ray, thanks for updating the title.

If your data is in A1:A100, then in B1 try:

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

Fill that down and you should be set.

[RayBox]

Hi Paul,

Thanks heaps, works a treat!

Cheers,
Ray

[RayBox]

Hi Paul,

Need some help with a mod to this formula. I know need to search for the occurrence of a symbol ~

When I insert the symbol into the formula is returns the "euro" whether it's there or not. Any ideas.

Thanks,

Ray

[Paul]

Hi Ray,

The tilda character (~) is a special character in the Find/Replace functionality of Excel. To search for a literal tilda within a string, use two of them in the formula, e.g.

=IF(ISNUMBER(SEARCH("~~euro",A1)),"euro","")

This will only result in "euro" if cell A1 contains "~euro" somewhere within its text.

To simply search for a tilda, use two tildas, e.g.

=IF(ISNUMBER(SEARCH("~~",A1)),"euro","")

This will return "euro" if a tilda is found anywhere in cell A1.