CODEWITHSUNDEEP
excelexcel-formula
SNT
SNT

Reputation: 1403

How do I split a cell and create new concatenated field?

So I have fields like Fruit-Apple-25 , Vegetable-Tomato-22 in a row. Can I create a new field where I can name it as Fr_Grp_App , Vg_Grp_Tm. What I am using now is to just select if its apple or tomato but not add the other prefixes. How can I do that.

if(not(iserror(find("Apple",D3))),"Apple",if(not(iserror(find("Tomato",D3))),"Tomato")

So if its Fruit then it should be Fr in the prefix and if its Vegetable it should be Vg as prefix

Upvotes: 0

Views: 48

Answers (1)

JNevill
JNevill

Reputation: 50064

You didn't ask for it, but I think doing this in a UDF will give you a lot more control over how you define these groups and allow you to scale up without getting bogged down in a really horrible string manipulating formula.

To do this, create a new Module in VBE and paste this in:

Public Function FoodGroup(foodString As String) As String
    'Variables
    Dim Group As String
    Dim Food As String

    'Get the incoming group and food name from the string
    Group = Split(foodString, "-")(0)
    Food = Split(foodString, "-")(1)

    'Handle group names
    Select Case Group
        Case "Fruit"
            Group = "Frt"
        Case "Vegetable"
            Group = "Veg"
    End Select

    'Handle Fruit Names
    Select Case Food
        Case "Tomato"
            Food = "Tm"
        Case "Apple"
            Food = "App"
    End Select

    'Return
    FoodGroup = Group & "-Grp-" & Food

End Function

Save your workbook and you can then use this new UDF in your workbook:

enter image description here

Upvotes: 3

Related Questions