Parsing text

R

ryno200sx

Hello-
This will probably be an easy one.....here are the details, any help
would be greatly appreciated:
I have a file that has designer fragrances in a column. The fragrances
are listed in the format Fragrance name by Manufcturer name (i.e.
Eternity by Calvin Klein, Fahrenheit by Christian Dior, etc). I need to
extract the designer name from the string. (Ex. "Eternity by Calvin
Klein" appears in the cell, I need the words "Calvin Klein"). I used
the following formula in an attempt to acheive this:

=IF(ISNUMBER(SEARCH(\"BY\",A1)),RIGHT(A1,LEN(A1)-SEARCH(\"BY\",A1)),A1)

The problem is when I run this formula, the result for the above
example is "y Calvin Klein"

I know there is probably an easier way to accomplish my desired
results. Any help would be greatly appreciated as I am trying to learn
formulas and VBA on the fly.

Thanks,
Ryno200sx
:confused:
 
K

Kieran

I think that the following should work
=IF(ISNUMBER(SEARCH("by",A1)),MID(A1,SEARCH("by ",A1)+3,256),A1)

Or (manually) copy the data to anther column.
Perform a serach & replace changing 'by' to ','.
Then use the text to columns wizard to process the list, using th
delimited option and the ',' option
 
Top