Split a column of text

A column of text can be split into multiple columns in two ways: by delimiter or by a number of characters. You split a column in the Query Editor.

Show

ShowLearn more about the Query Editor

You can access the Query Editor from the Navigator pane when connecting to a data source, or at any time from the Workbook Queries pane or the contextual Query ribbon tab for an existing query. To learn how to load the Query Editor, see Power Query Quick Start.

I want to


Split a column by delimiter

Split Column > By Delimiter parses a text value based on a common character into two or more columns. For example, a Name column as LastName, FirstName can be split into two columns based on the comma (,) character.

Using the Query Editor ribbon

  1. Click the column you want to split.
  2. In the Query Editor ribbon, click Split Column > By Delimiter.

Using the Query Editor context menu

  1. Right-click the column header you want to split.
  2. In the column context menu, click Split Column > By Delimiter.


To split a column by delimiter

  1. In the Split a column by delimiter dialog box:
    1. In the Select or enter a delimiter drop-down, select Colon, Comma, Equals Sign, Semicolon, Space, Tab, or Custom. Select Custom to specify any character delimiter.
    2. Select a Split option.
    3. Expand Show advanced options, and enter the Number of columns to split into.
  2. Click OK.
  3. Rename the new columns to more meaningful names. For more information about how to rename a column, see Rename a column.
  1. In the Split a column by delimiter dialog box:
    1. In the Select or enter a delimiter drop-down, select Colon, Comma, Equals Sign, Semicolon, Space, Tab, or Custom. Select Custom to specify any character delimiter.
    2. Select a Split option.
    3. Expand Show advanced options, and enter the Number of columns to split into.
  2. Click OK.
  3. Rename the new columns to more meaningful names. For more information about how to rename a column, see Rename a column.

Top of Page Top of Page

Split a column by position

Split Column > By Number of Characters parses a text value based on a character position within a text value.

Using the Query Editor ribbon menu

  1. Click the column you want to split.
  2. In the Query Editor ribbon, click Split Column > By Number of Characters.

Using the Query Editor context menu

  1. Right-click the column header you want to split.
  2. In the column context menu, click Split Column > By Number of Characters


To split a column by position

  1. In the Split a column by position dialog box:
    1. In the Number of character textbox, enter the number of characters used to split the text column.
    2. Select a Split option.
    3. Expand Show advanced options, and enter the Number of columns to split into.
  2. Click OK.
  3. Rename the new columns to more meaningful names. For more information about how to rename a column, see Rename a column.
  1. In the Split a column by position dialog box:
    1. In the Number of character textbox, enter the number of characters used to split the text column.
    2. Select a Split option.
    3. Expand Show advanced options, and enter the Number of columns to split into.
  2. Click OK.
  3. Rename the new columns to more meaningful names. For more information about how to rename a column, see Rename a column.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Excel 2010