How to Split Text into Different Columns in Google Sheets

If you’re looking to Split Text into Different Columns in Google Sheets, you have landed at the right place.

First, here’s a quick video featuring both methods:

Now, here’s a detailed explanation of the Google Sheets formula to Split Text into Different Columns:

#1 – By using SPLIT function

=SPLIT(A2,",")
  • SPLIT: a function that splits a text string into separate pieces based on a specified delimiter.
  • A2: the cell containing the text string to be split.
  • “,”: the delimiter used to split the text string into separate pieces.

#2 – By using ARRAYFORMULA and REGEXEXTRACT function

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A,"^(.+?),(.+?),(.+?),(.+?)$")))
  • ARRAYFORMULA: a function that allows a formula to be applied to an entire column of data instead of just one cell.
  • IFERROR: a function that allows for the handling of errors in a formula.
  • REGEXEXTRACT: a function that extracts text from a string that matches a regular expression pattern.
  • A2:A: the range of cells containing the text strings to be split.
  • “^(.+?),(.+?),(.+?),(.+?)$”: a regular expression pattern that matches four separate pieces of text separated by commas. The parentheses capture each piece of text as a separate group.

That’s it.

Other cool Google Sheets formulas:

And if you get stuck somewhere while applying the GSheets formula to Split Text into Different Columns, kindly let me know in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *

2 Comments

  1. george zimmer says:

    When you split names you end up with some last names in middle name column. When you move last names to Last Name column Sheets won’t NOT delete the last name in Middle Name column. Why is TEXTSPLIT persistent?

    I can’t paste a screenshot so I published to web.
    https://docs.google.com/spreadsheets/d/e/2PACX-1vRxLeOfPDoWn2-neAtWYQqzRb6Gg3KmBCuVwMVJ53vIQTsRPW9OXdzcnqyATCHtdrUggXm1IQ0fITVD/pubhtml?gid=0&single=true

    Reply