strip based on a range.

[topi1]

Book1
	A	B	C	D	E
1	Toyota Highlander is popular	Toyota	Highlander is poular		Mazda
2	General Knowledge	General	Knowledge		Honda
3	Mercedes Benz	Mercedes Benz			Toyota
4					Ford
5					General Motors
Sheet3

Hi, In the above example, I have data in the Columns A and E. Is it possible to have formula in B1 and C1 to see if the first word in A1 matches with any word in the column E, and if it does, put that word in B1 and the rest of A1 string in C1. If there is no match in the column E, then B1=A1 and C1 is blank. As in the example.
Thank you in advance.

 

[Peter_SSs]

Does this do what you want?

23 12 31.xlsm
	A	B	C	D	E
1	Toyota Highlander is popular	Toyota	Highlander is popular		Mazda
2	General Knowledge	General	Knowledge		Honda
3	Mercedes Benz	Mercedes Benz			Toyota
4					Ford
5					General Motors
First Word

Cell Formulas
Range	Formula
B1:B3	B1	=IF(ISNUMBER(MATCH("*"&LEFT(A1,FIND(" ",A1)-1)&"*",E:E,0)),LEFT(A1,FIND(" ",A1)-1),A1)
C1:C3	C1	=TRIM(SUBSTITUTE(A1,B1,""))

 

[topi1]

Does this do what you want?

23 12 31.xlsm
	A	B	C	D	E
1	Toyota Highlander is popular	Toyota	Highlander is popular		Mazda
2	General Knowledge	General	Knowledge		Honda
3	Mercedes Benz	Mercedes Benz			Toyota
4					Ford
5					General Motors
First Word

Cell Formulas
Range	Formula
B1:B3	B1	=IF(ISNUMBER(MATCH("*"&LEFT(A1,FIND(" ",A1)-1)&"*",E:E,0)),LEFT(A1,FIND(" ",A1)-1),A1)
C1:C3	C1	=TRIM(SUBSTITUTE(A1,B1,""))

Fantastic. Perfect. I should have thought of C1. Not that it would have helped without B1. Thank you so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[topi1]

How about this variation...
In the following example, there are texts in the column A and range of texts in the column E. I would like a vba or formula in the column B which displays part of the string in the column A which has a match with one of the cells in the column E. Variable number of rows. for both columns A and E. The matching string can be in the start, end or anywhere in between in the sentence. Been trying since yesterday but can't find any solution. Found a way to see if there is a match or not but can't find a way to extract matching text.
Thank you in advance.

Book1
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	Thursady			SUNDAY
3	Tomorrow is Friday				MONDAY
4	Yesterday was Wednesday, a hump day	Wednesday, a hump day			TUESDAY
5					WEDNESDAY, A HUMP DAY
6					THURSDAY
7
Sheet1

 

[Peter_SSs]

Would this suffice?

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			TUESDAY
5					WEDNESDAY, A HUMP DAY
6					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B4	B2	=IFNA(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$6&" "," "&A2&" "),$E$2:$E$6),"")

 

[topi1]

Would this suffice?

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			TUESDAY
5					WEDNESDAY, A HUMP DAY
6					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B4	B2	=IFNA(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$6&" "," "&A2&" "),$E$2:$E$6),"")

Thank you for your prompt response. Can't find IFNA in excel 2010. Looks like I need to get 2021 or 365. Is there a solution for 2010? Thank you.

 

[Peter_SSs]

Can't find IFNA in excel 2010. ... Is there a solution for 2010?

Sorry, forgot about that. Use IFERROR instead

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			TUESDAY
5					WEDNESDAY, A HUMP DAY
6					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$6&" "," "&A2&" "),$E$2:$E$6),"")

 

[topi1]

Sorry, forgot about that. Use IFERROR instead

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			TUESDAY
5					WEDNESDAY, A HUMP DAY
6					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$6&" "," "&A2&" "),$E$2:$E$6),"")

Brilliant. Thank you. I modified it for gaps in the column A with intermittent blank cells. Otherwise it was giving me "0" value in B column. Thanks.

 

[Peter_SSs]

I modified it for gaps in the column A with intermittent blank cells. Otherwise it was giving me "0" value in B column.

Intermittent gaps in column A shouldn't cause a zero unless you also have intermittent gaps in the column E data. Anyway, glad you got it sorted. This would be my small change to deal with that.

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4					TUESDAY
5	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			WEDNESDAY, A HUMP DAY
6
7					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$7&" "," "&A2&" "),$E$2:$E$7)&"","")