Split first word only if it is part of a range.

[topi1]

In the following example, I have a range of values in the column E. data in the column A. In the columns B and C I have entered formulas (arrived by working stepwise on several columns and then combining it). Everything works well and extracts first word from A into column B only if it is part of the range in the column E. And it leaves the rest in the column C.
Is it possible to have a vba which can do the same thing? Thank you.

rscripto11.xlsm
	A	B	C	D	E
42	GM #1		GM #1		One
43	Dodge #4		Dodge #4		Two
44	Ford #2		Ford #2		Three
45	Jeep #3		Jeep #3		Four
46	One Dodge	One	Dodge		Five
47	Two Dodge	Two	Dodge
48	Two Jeep	Two	Jeep
49	One Dodge	One	Dodge
50	One Jeep	One	Jeep
51	Two Jeep	Two	Jeep
Sheet6

Cell Formulas
Range	Formula
B42:B51	B42	=IF((COUNTIF($E$42:$E$51,IFERROR(LEFT(A42, FIND(" ", A42)-1), A42))=1),IFERROR(LEFT(A42, FIND(" ", A42)-1), A42),"")
C42:C51	C42	=IF((COUNTIF($E$42:$E$51,IFERROR(LEFT(A42, FIND(" ", A42)-1), A42))=1),IF((ISERROR(SUBSTITUTE(A42,LEFT(A42,FIND(" ",A42)),""))=FALSE),SUBSTITUTE(A42,LEFT(A42,FIND(" ",A42)),""),""),A42)

 

[topi1]

Sub jec()
 Dim ar, ar2, j As Long, jj As Long
 ar = Range("A42", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
 ar2 = Range("E42:E46")
 For j = 1 To UBound(ar)
   ar(j, 3) = ar(j, 1)
   For jj = 1 To UBound(ar2)
     If InStr(ar(j, 1), ar2(jj, 1)) Then
       ar(j, 2) = Split(ar(j, 1))(0)
       ar(j, 3) = Split(ar(j, 1))(1)
       Exit For
     End If
   Next
 Next
 Range("A42", Range("A" & Rows.Count).End(xlUp)).Resize(, 3) = ar
End Sub

@JEC It works great as long the value in E is not a number. Is there a solution? I tried to add some columns and tried to use TEXT formula, but it did not work. TY.

 

[mumps]

Can you post a new sheet that has some numbers?

 

[JEC]

Untested, try to use this line instead

If InStr(ar(j, 1), Cstr(ar2(jj, 1))) then

 

[topi1]

Untested, try to use this line instead

If InStr(ar(j, 1), Cstr(ar2(jj, 1))) then

Sorry, that didn't do the trick.

 

[topi1]

Can you post a new sheet that has some numbers?

I am so sorry. As I was creating XL2BB file, I realized that I had to extend the range in column E. Now it works like a charm. Even with numbers. Sorry about such stupid oversight and wasting yours and JEC's time. Thank you so much.

 

[mumps]

You are very welcome. Glad we could help.