MS Excel - Split Data Into Columns Based On a Delimiter


I have some data in an MS Excel 2007 cell that I want to split into multiple columns based on a delimiter.

For example take the following data in a single cell -

Dark Teal-44

I use a formula [SUBSTITUTE(A1,CHAR(10),";")] to remove the spaces (Alt+Enter) at the end of each line to to get the following -

Blue/Green-07;Dark Teal-44;Wine-63;Royal-41

Now I want to take the above data and split into 2 columns so that I get all the colors in one column with a "," [comma] between them and the dash numbers in the other column with a "," [comma] between them. So for example I want the following -

Blue/Green,Dark Teal,Wine,Royal = Column 1
-07,-44,-63,-41 = Column 2

I would like to point out that some of the cells in the column in question may have one color, two colors, three colors, etc. so each cell is not the same. I am attaching an image to illustrate what I am looking for.

Any help with this is greatly appreciated.

Thank you!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use the text to column feature in excel 2007.
It asks for a delimiter and will do what you are wanting, i believe.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello conquerdev,

it's a bit more complex than a simple Text to columns.

1. Do the replacement of char(10) with ";"
2. copy the result and paste special - Values
3. on that column perform a Text to Columns, and specify two delimiters, the semicolon and the "other" as the dash
Now the data will be in many columns, alternating between color and number
4. Finally, you need a formula to concatenate every other colum back into one cell.

See attached for an example for one row only.

conquerdevAuthor Commented:
Hello teylyn,

I had thought about what you suggested, but I have over 400 rows of data with some items with as many as 18 colors. I am looking into macros to see if I can write something that will do this for me. I just do not know enough about macros right now to do it, but maybe some research and playing with it will help. I will need to edit data like this regularly so was looking for a quick process for it. Do you have any experience with macros? Would a macro do what I am looking for if the code could be written for it?

Thank you!
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Patrick MatthewsCommented:

Assuming that your "colors" parts will never have digits, and that your "dash numbers" will always be a dash and then 1+ digits, then I think I have a Regular Expressions-based solution for you.  For background, please see my article:

Anyway, please add the code in the snippet below to a regular VBA module.  (You will need to ensure that you save your file as XLS or XLSM; the XLSX format does not support VBA.)

Then, assuming your data start in A2, use this formula to get a comma-delimited list of "colors":


and this formula for a comma-delimited list of "dash numbers":



Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    ' For more info, please see:
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
            Pos = CLng(Pos)
        End If
    End If
    ' Evaluate ReturnType
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    ' Create instance of RegExp object if needed, and set properties
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    ' Test to see if there are any matches
    If RegX.Test(LookIn) Then
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            RegExpFind = Answer
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    ' If there are no matches, return empty string
        RegExpFind = ""
    End If
    ' Release object variables
    Set TheMatches = Nothing
End Function

Function ExcelJoin(SourceArray As Variant, Optional Delimiter As String = "")
    ExcelJoin = Join(SourceArray, Delimiter)
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
conquerdevAuthor Commented:
Hello Patrick,

I have added the code your provided into VBA and made sure my Excel file was saved as a .xlsm file. I have all macros enabled and the "trust access to the VBA project object model" checked. However, when I try to use the expression your provided I am getting a "Name" error. It is indicating an "Invalid Name Error". I did look at your article and I think this will work for me. I have not worked with this much so is there anything I am missing.

Thank you!
Patrick MatthewsCommented:
Are you sure that you put that code in a "regular" module, and not a Sheet module or the ThisWorkbook module?

It is working for me :)
conquerdevAuthor Commented:
Hello Patrick,

I am a total newbie at this so I am not sure I am doing it right :-). OK, I have made sure it is in a regular module. I am attaching a few screen shots to make sure I am not doing something foolish.

Thank you!
Patrick MatthewsCommented:
Try renaming the module; VBA gets confused when you use the same name for the module as you do for a sub or function :)
conquerdevAuthor Commented:
Really appreciate the help. It is working great! Thank you Patrick for the additional assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.