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  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.

Split a column by delimiter

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 Home > Split 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.

Split a column by number of characters

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.

Split a column by positions

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 Home> Split 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.

Split a column by letter case combinations

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 Home > Split Column > By Lowercase to Uppercase.

   • Select Home > Split 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.

Split a column by digit and non-digit combinations

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 Home > Split Column > By Lowercase to Uppercase.

   • Select Home > Split 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)