CODEWITHSUNDEEP
excel
NaCl
NaCl

Reputation: 1

Finding text within Cell and output results to separate columns

I have data within excel that looks like the below, row by row, but all information in column A. 

A
---------------------
Yellow Car Small
---------------------
Red Bike Big Big
---------------------
Big Yellow Car
---------------------
Yellow Big Motorbike

I need output into column B, what colour it is, C what size it is, and D what type.

Where do I start?

Ive made a list for each category 

Colour | Type      | Size
-------|-----------|------
Blue   | Car       | Small
-------|-----------|------
Yellow | Bike      | Big
-------|-----------|------
Red    | Motorbike | Large

but i think im missing something.

Upvotes: 0

Views: 42

Answers (1)

dwirony
dwirony

Reputation: 5450

So, this is a tiny bit complicated, because VBA doesn't know what a color is, what a vehicle is, or what a size is, so you need to tell it. Unfortunately, the only way you can really do that is by placing values in to pre-set arrays for which you can guide it:

Sub splititup()

Dim myarray() As String

For i = 1 To 4 'or use lastrow for a dynamic range
    myarray = Split(Range("A" & i).Value, " ")
    Range("B" & i).Value = myarray(0)
    Range("C" & i).Value = myarray(1)
    Range("D" & i).Value = myarray(2)
Next i

Dim mycolors() As Variant, myvehicles() As Variant, mysizes() As Variant

mycolors = Array("Red", "Yellow", "Blue", "Green", "Orange", "Purple")
mysizes = Array("Small", "Medium", "Large", "Big")
myvehicles = Array("Boat", "Car", "Bike", "Motorbike", "Truck")

'color, size, type

Dim Value1 As String, Value2 As String, Value3 As String

For i = 1 To 4
    Value1 = Range("B" & i).Value
    Value2 = Range("C" & i).Value
    Value3 = Range("D" & i).Value

    If Not IsInArray(Value1, mycolors) Then
        If IsInArray(Value1, mysizes) Then
            Range("C" & i).Value = Value1
        Else
            Range("D" & i).Value = Value1
        End If
    Else
        Range("B" & i).Value = Value1
    End If

    If Not IsInArray(Value2, mycolors) Then
        If IsInArray(Value2, mysizes) Then
            Range("C" & i).Value = Value2
        Else
            Range("D" & i).Value = Value2
        End If
    Else
        Range("B" & i).Value = Value2
    End If

    If Not IsInArray(Value3, mycolors) Then
        If IsInArray(Value3, mysizes) Then
            Range("C" & i).Value = Value3
        Else
            Range("D" & i).Value = Value3
        End If
    Else
        Range("B" & i).Value = Value3
    End If

Next i

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

This splits the values up from column A into column B, C & D, then tests each value against it's corresponding array to decide how to sort it:

Before&After

I really don't know any other way to do it. If anyone else has a better solution, please do share.

Upvotes: 1

Related Questions