locked
Split up comma separated values RRS feed

  • Question

  • Hi All,

    Good Morning!!

    I am looking for a sub routine which will split up comma delimited values and display them to the next column.

    E.g. Column A of Sheet1 having the following data

    Input
    Louiyt,Juiyt,Dillipo,Keo
    fruito,Julim
    Kiuycliea
    Jimm,Opium,Kuiop,Noma

    If I run the macro, the output view should be displayed in Column B as follows

    Output
    Louiyt
    Juiyt
    Dillipo
    Keo
    fruito
    Julim
    Kiuycliea
    Jimm
    Opium
    kuiop
    Noma

    Please let me know if it is possible with VBA

    Thanks

    Saturday, July 25, 2015 6:24 PM

Answers

  • Here you go. You can change the constants at the beginning if the layout of the data changes.

    Sub SplitData()
        Const SrcCol = 1 ' A
        Const TrgCol = 2 ' B
        Const FirstRow = 1
        Dim LastRow As Long
        Dim SrcRow As Long
        Dim TrgRow As Long
        Dim TheVal As String
        Dim TheArr As Variant
        Dim Num As Long
        Application.ScreenUpdating = False
        TrgRow = FirstRow
        LastRow = Cells(Rows.Count, SrcCol).End(xlUp).Row
        For SrcRow = FirstRow To LastRow
            TheVal = Cells(SrcRow, SrcCol).Value
            TheArr = Split(TheVal, ",")
            Num = UBound(TheArr) + 1
            Cells(TrgRow, TrgCol).Resize(RowSize:=Num).Value = Application.Transpose(TheArr)
            TrgRow = TrgRow + Num
        Next SrcRow
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ryguy72 Saturday, July 25, 2015 6:59 PM
    • Marked as answer by Ed_Dao Saturday, July 25, 2015 8:07 PM
    Saturday, July 25, 2015 6:51 PM
  • That is because Excel sees the commas as thousand separators. Either format the cells as Text before entering the data, or prefix them with an apostrophe, e.g. '300,400,500. The ' tells Excel to treat the value as text.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ed_Dao Saturday, July 25, 2015 8:07 PM
    Saturday, July 25, 2015 7:45 PM

All replies

  • Here you go. You can change the constants at the beginning if the layout of the data changes.

    Sub SplitData()
        Const SrcCol = 1 ' A
        Const TrgCol = 2 ' B
        Const FirstRow = 1
        Dim LastRow As Long
        Dim SrcRow As Long
        Dim TrgRow As Long
        Dim TheVal As String
        Dim TheArr As Variant
        Dim Num As Long
        Application.ScreenUpdating = False
        TrgRow = FirstRow
        LastRow = Cells(Rows.Count, SrcCol).End(xlUp).Row
        For SrcRow = FirstRow To LastRow
            TheVal = Cells(SrcRow, SrcCol).Value
            TheArr = Split(TheVal, ",")
            Num = UBound(TheArr) + 1
            Cells(TrgRow, TrgCol).Resize(RowSize:=Num).Value = Application.Transpose(TheArr)
            TrgRow = TrgRow + Num
        Next SrcRow
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ryguy72 Saturday, July 25, 2015 6:59 PM
    • Marked as answer by Ed_Dao Saturday, July 25, 2015 8:07 PM
    Saturday, July 25, 2015 6:51 PM
  • Hans, you are awesome!!!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, July 25, 2015 6:59 PM
  • Hi Hans,

    Thanks for your quick reply. It is working fine with if A column is populated with character value but if I used the same logic for numeric values data is getting concatenated. 

    I have used the following data as input:

    100,200
    300,400,500
    600
    700,800

    I am getting the output as follows

    100200
    300400500
    600
    700800

    Any specific reason for that?

    Saturday, July 25, 2015 7:05 PM
  • That is because Excel sees the commas as thousand separators. Either format the cells as Text before entering the data, or prefix them with an apostrophe, e.g. '300,400,500. The ' tells Excel to treat the value as text.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ed_Dao Saturday, July 25, 2015 8:07 PM
    Saturday, July 25, 2015 7:45 PM