Splitting Cells to Individual Columns

Written by Allen Wyatt (last updated August 5, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


10

Garret received a bunch of data from his corporate computer system, and he needs to split it out into columns. A single cell might look like this:

"Working, August 2023", "Research, March 2024, Pending", "Offer, July 2023"

All text between each pair of quote marks should be in its own column, so he needs to split this to three columns and get rid of the quote marks. Garret thought he could use Text to Columns to split the original cell, but he cannot do it based on commas as a delimiter (since there are six commas in the source cell). Garret wonders about the easiest way to split out the nearly 5,000 cells he needs to work with.

There are several ways that this task can be approached. Perhaps the easiest way is to follow these general steps:

  1. Load the data into Excel. (You should now have your 5,000 cells all in column A.)
  2. Do a Find and Replace operation, replacing a quote mark followed by a comma (",) with a quote mark followed by a tilde ("~). If the tilde is used elsewhere in your data, you can use a different character that is not used within your data. The idea is that after the Find and Replace is done, you now have a unique single-character delimiter in place in your data.
  3. Use the Text to Columns tool to separate your data based on the tilde or special character.

These three general steps work because the only place where a comma appears right after a quote mark is between what will eventually be columns. If you replace that comma (the one right after the quote mark) with a delimiter other than a comma, then you can use Text to Columns.

If you don't want to do a Find and Replace operation, you can still use Text to Columns. Follow these steps:

  1. Load the data into Excel. (You should now have your 5,000 cells all in column A.)
  2. Select all 5,000 cells in column A.
  3. Display the Data tab of the ribbon.
  4. Click the Text to Columns tool. Excel starts the Text to Columns Wizard and displays the dialog box for step 1.
  5. Make sure the Delimited radio button is selected. (See Figure 1.)
  6. Figure 1. Convert Text to Columns Wizard, step 1.

  7. Click the Next button. The wizard progresses to step 2.
  8. Clear all the checkboxes under Delimiters, with the exception of Other. That one should be selected, and in the box to the right of the checkbox enter a quote mark.
  9. Choose None using the Text Qualifier drop-down list. (See Figure 2.)
  10. Figure 2. Convert Text to Columns Wizard, step 2.

  11. Click the Next button. The wizard progresses to step 3. Note that at the bottom of the dialog box, the current state of your data is shown. (This section of the dialog box is called Data Preview.) (See Figure 3.)
  12. Figure 3. Convert Text to Columns Wizard, step 3.

  13. Select one of the non-data columns in the data depiction at the bottom of the dialog box.
  14. Click the Do Not Import Column radio button.
  15. Repeat steps 10-11 for each of the non-data columns. (These columns contain the commas that were originally between each data grouping in your original data.)
  16. Click the Finish button. The wizard completes its work, and your data is in the proper columns.

You can also, if you are using Excel 2010 or a later version, use Power Query to import your data. Rather than walk through the steps necessary to set up the query, I'll choose to include here just the MCode for the query. (You can load the MCode using the advanced Power Query Editor.)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(""", ", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","""","",Replacer.ReplaceText,{"Column1.1", "Column1.2", "Column1.3", "Column1.4"})
in
    #"Replaced Value"

The MCode will handle up to four possible columns being extracted from the original data. It does require that you format your original data as a table and that it have the name Table1.

If you would like a macro to do the data conversion for you, then the following will do the trick for any number of final columns:

Sub ParseIt()
    Range(Selection, Selection.End(xlDown)).Select

    With Selection
        .Replace What:=""", """, Replacement:="~", LookAt:=xlPart
        .Replace What:="""", Replacement:="", LookAt:=xlPart
        .TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
          Other:=True, OtherChar:="~", _
          FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    End With
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.AutoFit
    ActiveCell.Select
End Sub

In order to use the macro, all you need to do is to load your data and then select the first cell in the data.

Finally, if you are using Excel in Microsoft 365, Excel 2019, or Excel 2021, then you can use a formula to pull apart your original data. Assuming your data starts in cell A1, place this formula in a row-1 cell to the right of your data:

=SUBSTITUTE(TEXTSPLIT(A1,""", """),"""","")

Copy the formula down as far as necessary and you'll have your split-out data.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13906) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Named Ranges in a Macro

Named ranges are a great capability provided by Excel. You can define all sorts of named ranges in a workbook, but how do ...

Discover More

Moving Files or Folders

A common operation within Windows is to move files and folders from one location to another. Here are the two major ways ...

Discover More

Pasted Text Not Formatted as Expected

Copying and pasting information is a common practice in Word. How the program should handle formatting (especially styled ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Quickly Filling a Column

Excel has a great (and little known) shortcut for filling a column with information. It comes in very handy when you need ...

Discover More

Copying a Cell without Formatting

When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy ...

Discover More

Weird Pasting Behavior

The Clipboard is integral to editing data in your worksheets. What happens, though, when the Clipboard doesn't allow you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2023-08-17 12:43:58

Willy Vanhaelen

@Tomek
It is because in your previous comment you pointed to the right direction that I started to try out several combinations that lead to this easy and simple procedure. I also got a better insight in the text to column wizard and the CSV file format I hardly used in the past.

Obviously, an update of this tip is unavoidable.


2023-08-15 11:41:28

Tomek

@Willy Vanhaelen and @Allen:
Re: Comment of 2023-08-14 12:06:23

BINGO!
No need to do any replacement!
And good learning about how the text inside the quotes is treated when splitting Text-to-Columns.

@Allen:
It may be worthwhile to update your tip with this simplest solution.


2023-08-14 12:06:23

Willy Vanhaelen

@Tomek
While trying out what you explained in your second paragraph, I discovered an even much easier procedure:
I selected the comma AND space as delimiters AND checked the quote mark as text qualifier and then clicked Finish and the job was done !!!
It seems that Excel sees the comma/space combination as the delimiter but only if they are outside the quote marks. So, there is even no need for the tilde ~.

There is even no need for a macro either.


2023-08-13 20:43:57

Tomek

Hopefully a better quality screenshot (see Figure 1 below)

Figure 1. 


2023-08-13 20:35:58

Tomek

@Willy Vanhaelen comment of 2023-08-05 12:03:17

As I mentioned in my comment, leaving the space in will interfere with the Text-to-Columns operation. If you remove the spaces after the commas, and do Text-toColumn with ~ as delimiter ***AND*** quotation mark (") as Text qualifier - the quotation marks will be removed; no need to remove them by another Find and Replace. If you leave the spaces after the commas, only the first pair of the quotation marks will be removed, the rest, as the quotation mark in each of them will be preceded by a space, will get Excel "confused" and the remaining quotation marks will stay.

I got some success without removing the spaces, but with setting the delimiter to "~" and <space> at the same time, with "Treat consecutive delimiters as one" checked ***AND*** (") as text qualifier. Somewhat surprisingly, this did not treat the spaces inside the quotation marks as delimiters! I guess the text within quotes was identified as one indivisible piece. See a sample screenshot - (see Figure 1 below)

Figure 1. 


2023-08-13 12:22:14

Willy Vanhaelen

To use the macro, select your one column range, and run it.


2023-08-13 12:13:44

Willy Vanhaelen

This tip’s macro does the job very well. It occurred to me though that the two functions (Replace and TextToColumns) used in the For Each loop are in fact Excel functions. So, I thought that, as an experiment, it would be interesting to make a macro based on pure VBA functions. I managed to do it by using 2 InStr(Rev) and 3 Mid functions. This is the result:

Sub txt2col()
Dim cell As Range, X As Integer, Y As Integer, Z As Integer
X = Selection.Column
For Each cell In Selection
    Y = InStrRev(cell, """, """)
    Cells(cell.Row, X + 2) = Mid(cell, Y + 4, Len(cell) - Y - 4)
    Z = InStr(cell, """, """)
    Cells(cell.Row, X + 1) = Mid(cell, Z + 4, Y - Z - 4)
    cell = Mid(cell, 2, Z - 2)
Next cell
Y = Selection.Row
Range(Cells(Y, X), Cells(Y, X + 2)).EntireColumn.AutoFit
End Sub


2023-08-06 10:37:28

J. Woolley

The Tip's final dynamic array formula
=SUBSTITUTE(TEXTSPLIT(A1,""", """),"""","")
refers to "Excel in Microsoft 365, Excel 2019, or Excel 2021," but TEXTSPLIT is only in 365 and 2019 does not support dynamic arrays.
My Excel Toolbox includes the following function:
=SplitText(Text,[Delimiter],[CaseSensitive],[Limit],[Remainder])
This returns an array of substrings after dividing Text at each occurrence of Delimiter; the default Delimiter is a space character, but any string is permitted. If CaseSensitive is FALSE (default), alphabetic case of Delimiter is ignored. If Limit = -1 or TRUE (default), all substrings are returned; otherwise, the result has Limit substrings (which must be > 0). If Remainder is FALSE (default) and Limit > 0, any remainder is ignored; otherwise, the last substring will include the remainder. Here is an abbreviated version:

Public Function SplitText(Text As String, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = False, Optional Limit As Integer = -1, Optional Remainder As Boolean = False) As String()
    Dim n As Integer, A() As String
    n = IIf(Remainder Or (Limit < 1), Limit, (Limit + 1))
    A = Split(Text, Delimiter, n, _
        IIf(CaseSensitive, vbBinaryCompare, vbTextCompare))
    n = UBound(A)
    If (Not Remainder) And (Limit > 0) And (n >= Limit) Then _
        ReDim Preserve A(n - 1)
    SplitText = A
End Function

Assuming the Tip's data starts in cell A1 and its quoted substrings are separated by comma plus space (2 characters), this Excel 2021+ dynamic array formula will spill the split results into empty cells on the right:
=SUBSTITUTE(SplitText(A1,""", """),"""","")
With CHAR(34) as quotation mark, CHAR(44) as comma, and CHAR(32) as space, here is the equivalent formula, which might be easier to understand:
=SUBSTITUTE(SplitText(A1,CHAR(34)&CHAR(44)&CHAR(32)&CHAR(34)),CHAR(34),"")
The Tip's data contains only 3 quoted substrings; therefore, the following CSE array formula can be used in older versions of Excel by selecting 3 empty columns and pressing Ctrl+Shift+Enter:
=SUBSTITUTE(SplitText(A1,""", """,,3,TRUE),"""","")
And here is the equivalent formula:
=SUBSTITUTE(SplitText(A1,CHAR(34)&CHAR(44)&CHAR(32)&CHAR(34),,3,TRUE),CHAR(34),"")
As stated in the Tip, "Copy the formula down as far as necessary and you'll have your split-out data." For the CSE array formula, you must select all 3 columns before copying.
See https://sites.google.com/view/MyExcelToolbox/


2023-08-05 12:03:17

Willy Vanhaelen

@Tomek
You are right, the first method doesn’t remove the space following the comma but it also doesn’t get rid of the quote marks. Here is the correction:

2. Do a Find and Replace operation, replacing a quote mark followed by a comma AND A SPACE (", “) by simply a tilde (~) ...
2 bis. CONTINUE IN FIND AND REPLACE TO REMOVE ALL THE QUOTE MARKS: in “Find What:” replace (", ) with one quote mark and delete the ~(tilde) in “Replace with:”.


2023-08-05 07:20:03

Tomek

In the first method, it is beneficial to replace also the space following the comma, i.e., use (", ) as Find what, and replace it with ("~). This will remove the space after the comma, that is present in Garret's data. If you leave the space in, it may interfere with the split.
Also, when selecting the delimiter (~), make sure that (") is selected as Text qualifier; this will result with quotation marks to be removed from the split columns.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.