refer a cell text in b1 and extract remaing text from a1
 

I got text in a1 like "Hydro carbon resin Petrolac 120" and in b1 like
"Petrolac 120". Now I need in c1 as "Hydro carbon resin". A formula needed to
extract the list in a with reference to list in b in c column.

refer a cell text in b1 and extract remaing text from a1
 

Hi

In C1
=SUBSTITUTE(A1,B1,"")

--
Regards
Roger Govier

Narnimar wrote:
I got text in a1 like "Hydro carbon resin Petrolac 120" and in b1 like
"Petrolac 120". Now I need in c1 as "Hydro carbon resin". A formula needed to
extract the list in a with reference to list in b in c column.

refer a cell text in b1 and extract remaing text from a1
 

Hi,

A few examples would have helped but this works for your single example. The
second formula is dependant on the first so both will need to be entered for
this to work.

Put this in B1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198))

and in C1

=TRIM(SUBSTITUTE(A1,B1,""))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Narnimar" wrote:

I got text in a1 like "Hydro carbon resin Petrolac 120" and in b1 like
"Petrolac 120". Now I need in c1 as "Hydro carbon resin". A formula needed to
extract the list in a with reference to list in b in c column.

refer a cell text in b1 and extract remaing text from a1
 

Formula for C1: =LEFT(A1,FIND(B1,A1)-1)

"Narnimar" wrote:

I got text in a1 like "Hydro carbon resin Petrolac 120" and in b1 like
"Petrolac 120". Now I need in c1 as "Hydro carbon resin". A formula needed to
extract the list in a with reference to list in b in c column.

refer a cell text in b1 and extract remaing text from a1
 

Mr Mike H

Mr Narnimar want to a1 like "Hydro carbon resin Petrolac 120" and in
b1 "Petrolac 120". c1 "Hydro carbon resin". But Your formula is working only
in b1 Number & c1 is alpha. but mr narnimar want in cell c1 alpha with number.
please help him & me.
Thanks advance
raju
mumbai

"Mike H" wrote:

Hi,

A few examples would have helped but this works for your single example. The
second formula is dependant on the first so both will need to be entered for
this to work.

Put this in B1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198))

and in C1

=TRIM(SUBSTITUTE(A1,B1,""))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Narnimar" wrote:

I got text in a1 like "Hydro carbon resin Petrolac 120" and in b1 like
"Petrolac 120". Now I need in c1 as "Hydro carbon resin". A formula needed to
extract the list in a with reference to list in b in c column.