Split a column of text (Power Query)

In Power Query, a column of text can be split into multiple columns and in a number of different ways to achieve the results you want. By default, the name of the new columns contains the same name as the original column with a suffix of a period (.) and a number that represents each split section from the original column. You can then rename the column.

Note    The expand icon  Expand in a column header is not used to split a column. It is used to expand structured columns, such as List, Record, or Table. For more information see Work with a List, Record, or Table structured column.

You can split a column with a text data type into two or more columns by using a common delimiter character. For example, a  Name column that contains values written as <LastName>, <FirstName> can be split into two columns using the comma (,) character.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the column you want to split. Ensure that it is a text data type.

  3. Select HomeSplit Column > By Delimiter. The Split a column by delimiter dialog box appears.

  4. In the Select or enter a delimiter drop-down, select Colon, Comma, Equals Sign, Semicolon, Space, Tab, or Custom. You can also select Custom to specify any character delimiter.

  5. Select a Split at option:

    • Left-most delimiter    If there are several delimiters, the first split column is based on the delimiter farthest to the left and the second split column is based on the rest of the characters on its right.

    • Right-most delimiter   If there are several delimiters, the second split column is based on the delimiter farthest to the right and the first split column is based on the rest of the characters on its left.

    • Each occurrence of the delimiter    If there are several delimiters, split each column by the delimiter. For example, if you have three delimiters, you end up with four columns.

  6. Select Show advanced options, and the enter the number of columns or rows to split into.

  7. If you choose Custom in Select or enter a delimiter drop-down list, you may need to enter an alternative quote character or a special character.

  8. Select OK.

  9. You may want to rename the new columns to more meaningful names. For more information see Rename a column.

You can split a column with a text data type into two or more columns by using the number of characters within a text value.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the column you want to split. Ensure the column is a text data type.

  3. Select Home Split Column > By Number of Characters. The Split a column by Number of Characters dialog box appears.

  4. In the Number of characters textbox, enter the number of characters used to split the text column.

  5. Select a Split option:

    • Once, as far left as possible    The first split column is based on the number of characters counting from the left and the second split column is based on the rest of the characters on the right.

    • Once, as far right as possible    The second split column is based on the number of characters counting from the right  and the first split column is based on the rest of the characters on the left.

    • Repeatedly    If the column has many characters, split into many columns based on the number of characters. For example, if the original column has 25 characters and you specify the number of characters as 5, you end up with five new columns, each with 5 characters.

  6. Select Show advanced options, and the entern the number of columns to split into.

  7. Select OK.

  8. You may want to rename the new columns to more meaningful names. For more information, see Rename a column.

You can split a column by specifying fixed numeric positions of characters.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the column you want to split. Ensure the column is a text data type.

  3. Select HomeSplit Column > By Positions. The Split Column by Positions dialog box appears.

  4. In the Positions box, enter the position numbers to split the text column. For example, enter 5, 10, 15 to split the column into 3 columns of 5 characters.

  5. Optionally, select Advanced options, and then select one of the following:

    • Columns    Each column length is based on the the positions you specified. This is the default action.

    • Rows    Instead of a new column, a new row is added based on the the positions you specified. For example, enter 5, 10, 15 to split the column into 3 rows of 5 characters.

  6. Select OK.

  7. You may want to rename the new columns to more meaningful names. For more information, see Rename a column.

You can split a column based on case letter combinations, lowercase to uppercase or uppercase to lowercase:

Lowercase to uppercase    For every instance of two consecutive letters, the first being lowercase and the second being uppercase, split the original column so that the second split column begins with the uppercase letter. For example:

Original column

First split column

Second split column

AirPlane

Air

Plane

AngelFish

Angel

Fish

BallPark

Ball

Park

Uppercase to lowercase    For every instance of two consecutive letters the first being uppercase and the second being lowercase, split the original column so that the second split column begins with the lowercase letter. For example:

Original column

First split column

Second split column

Iphone

I

phone

Ipad

I

pad

Ebay

E

bay

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the column you want to split. Ensure that it is a text data type.

  3. Do one of the following: 

    • Select HomeSplit Column > By Lowercase to Uppercase.

    • Select HomeSplit Column > By Uppercase to Lowercase.

  4. You may want to rename the new columns to more meaningful names. For more information see Rename a column.

You want to split a column based on digit and non-digit combinations, digit to non-digit or non-digit to digit.

Digit to non-digit    For every instance of two consecutive characters, the first being a digit and the second being a non-digit, split the original column so that the second split column begins with the non-digit character. For example:

Original column

First split column

Second split column

123Shoes

123

Shoes

456Gloves

456

Gloves

789Scarf

789

Scarf

Non-digit to digit    For every instance of two consecutive characters, the first being non-digit and the second being a digit, split the original column so that the second split column begins with the digit character. For example:

Original column

First split column

Second split column

123Shoes

Shoes

123

456Gloves

Gloves

456

789Scarf

Scarf

789

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the column you want to split. Ensure that it is a text data type.

  3. Do one of the following: 

    • Select HomeSplit Column > By Lowercase to Uppercase.

    • Select HomeSplit Column > By Uppercase to Lowercase.

  4. You may want to rename the new columns to more meaningful names. For more information see Rename a column.

See Also

Power Query for Excel Help

Add or change data types

Split columns by delimiter (docs.com)

Split columns by number of characters (docs.com)

Split columns by positions (docs.com)

Split columns by lowercase to uppercase (docs.com)

Split columns by uppercase to lowercase (docs.com)

Split columns by non-digit to digit (docs.com)

Split columns by digit to non-digit (docs.com) 

Splitter functions (docs.com)

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×