VBA instead of a nested IF function or Vlookup

[Argh_Work]

Hi All,

I'm trying to hand off a large worksheet that has a couple of really slow steps. To make it as easy as possible for my colleague I am trying to refine some of the steps.
I was hoping someone could help me with a VBA code for the below?

In the worksheet column F is blank. I use it to Group the information from column E into central teams.
For instance
Blue Green goes to Blue
Yellow Yellow goes to Yellow
Blue Purple goes to Blue

I can't create the VBA code on my own but I am able to generally customise from an example. I'd really appreciate it if someone could have a look at the scenario for me.

If necessary I am happy to include a table on a second worksheet like the 2nd image below.

Thanks so much for your help,

 

[kevin9999]

Please try the following on a copy of your workbook. Put the code in a standard module & run when the sheet is active.

Option Explicit
Sub Into_Teams()
    Dim a, b, i As Long
    a = Range("E2", Cells(Rows.Count, "E").End(xlUp))
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To UBound(a, 1)
        Select Case UBound(Split(a(i, 1), " "))
            Case 0
                b(i, 1) = a(i, 1)
            Case 1
                b(i, 1) = Left(a(i, 1), InStr(a(i, 1), " ") - 1)
            Case Else
                b(i, 1) = "Rainbow"
        End Select
    Next i
    Range("F2").Resize(UBound(b, 1)).Value = b
End Sub

 

[Coyotex3]

Another option:

Sub ColorBeforeSpace()
Dim LR As Long

LR = Range("E" & Rows.Count).End(xlUp).Row

    Range("F2:F" & LR).FormulaR1C1 = "=LEFT(RC[-1],FIND("" "",RC[-1]))"
    Range("F2:F" & LR).Value = Range("F2:F" & LR).Value
End Sub

 

[kevin9999]

@Coyotex3 your suggestion will return an error if there's only one colour in the cell (see second image in post #1) and also will not return "Rainbow" if more than 2 colours are in the cell.

 

[Coyotex3]

@Coyotex3 your suggestion will return an error if there's only one colour in the cell (see second image in post #1) and also will not return "Rainbow" if more than 2 colours are in the cell.

Good call! Definitely did not account for this!

 

[Argh_Work]

Thank you all for taking the time to respond @Coyotex3 as @kevin9999 stated is pulling an error as pictured below.
Is there a suggestion to avoid this? Thanks again everyone.

 

[kevin9999]

Did you try the code in post #2? When I run it, I get this:

Book1
	E	F
1	Sub centre	Team
2	Red	Red
3	Blue Orange	Blue
4	Yellow Yellow	Yellow
5	Yellow Yellow	Yellow
6	Blue Purple	Blue
7	Purple Dinosaur	Purple
8	Pink Purple Orange	Rainbow
9	Pink Purple Orange	Rainbow
10	Red	Red
11	Red	Red
12	Red	Red
13	Blue Orange	Blue
14	Yellow Yellow	Yellow
15	Yellow Yellow	Yellow
16	Blue Purple	Blue
17	Purple Dinosaur	Purple
18	Pink Purple Orange	Rainbow
19	Pink Purple Orange	Rainbow
20	Red	Red
21	Red	Red
Sheet1