none
Formula to Split data based on Special Caharcters RRS feed

  • Question

  • Hi All,

    I have data in columns which are in the Below format shown in 1 st columns. I need the data to be split as show on the right in 3 different columns. The order of "/" remains constant, but data can change. So i would need data from 5th "/" from right and going on.

    TestFolder/Region/ASEAN/SG/Technology/Active/ ASEAN SG Technology
    TestFolder/Region/ASEAN/TH/Technology/Active/ ASEAN TH Technology

    Any help would be great


    Regards, PS

    Thursday, July 23, 2015 10:37 AM

Answers

  • Try the following UDF

    Function SpecialSplit(cell As Range, del As String, pos As Integer) As String
        SpecialSplit = Split(cell.Value, del)(pos) ' Note that pos is the number from LEFT
    End Function


    So if "TestFolder/Region/ASEAN/SG/Technology/Active/" is in cell A1 and you want "ASEAN" formula would be "=SpecialSplit(A1,"/",2)"
    Thursday, July 23, 2015 12:52 PM

All replies

  • Try the following UDF

    Function SpecialSplit(cell As Range, del As String, pos As Integer) As String
        SpecialSplit = Split(cell.Value, del)(pos) ' Note that pos is the number from LEFT
    End Function


    So if "TestFolder/Region/ASEAN/SG/Technology/Active/" is in cell A1 and you want "ASEAN" formula would be "=SpecialSplit(A1,"/",2)"
    Thursday, July 23, 2015 12:52 PM
  • Hi Pritam_Shetty,

    First, please refer to John’s solution.

    Secondly, you may consider the Text To Columns feature in excel.

    # How to parse data using the Text To Columns command in Excel

    https://support.microsoft.com/en-us/kb/214261

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 24, 2015 2:55 AM
    Moderator