Split Column by Carriage Return in Power Query

Split Column By Carriage Return

Scraping content from the web is getting easier and easier. Sometimes for the data to be useful you need to break the data up into multiple lines. Today’s post explores how you can split column data by Carriage Return to separate the cell contents into different rows.

Recently I tried retrieving the share prices of an investment fund from a website. Power Query imported all the share prices neatly. Yet all the data resides in a single cell, separated by carriage returns.

Import data

The share prices are available on a public website. To get them into Power Query

  • Open Power Query -> Select new source -> from Web

A pop up will open that allows you to fill in a website.

Get From Web Address

You end up at below screen.

Data Selection
  • Select the Table called “Document” -> press OK
Imported table object

Power Query now imports the above line. By itself the line is not very useful. Yet when you look closer, you see that the column ‘Data’ includes a Table object. You can use this Table Object to navigate to the part of the table containing the share prices. You can find more information on Table Objects here.

It may take some trial and error to find the right table. Perform the following steps:

  • Click the word Table in the column ‘Data’
  • Click the word Table in the column ‘Children’
  • Click the word Table in the column ‘Children’ in the Row containing the name Header (row 2)
  • Keep clicking the word Table in the most upper row in the column called ‘Children’

The result is the table below.

Source Data

Split Column by Carriage Return

At first sight, the results look pleasing. The Column named Text contains all the share prices we requested. Yet all the data resides in a single row. So let’s try to split the cell contents into rows.

Split by Delimiter
  • In the ribbon select Split Column -> By Delimiter

Below menu appears.

Split By Delimiter Menu
  1. As delimiter, select –Custom–
  2. Select Each occurrence of the delimiter
  3. Split into Rows
  4. Select Split into special characters
  5. From the drop down menu make sure to select Carriage Return

As result the data is now split into rows.

Cell Contents split into Rows

Special Characters

As the earlier drop down showed, you split your cell contents by other special characters. Below you find the list of special characters followed by their M-code equivalents.

  • Tab: #(tab)
  • Carriage Return: #(cr)
  • Line Feed: #(lf)
  • Carriage Return and Line Feed: #(cr)#(lf)
  • Non-Breaking Space: #(00A0)

Wrap up

Most people are aware they can split a cell into columns. But many don’t know you can split cells into rows as well. This post showed how you can split cell contents into rows.

I hope you liked this post. If you did, I would love it if you shared it with your network. And please leave a comment below with questions or suggestions.

Rick de Groot
About Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “Split Column by Carriage Return in Power Query”

  1. Hello’ Mr Rick de Groot you write a very help full and informative article for student who love to learn a excel in online web Thx Rick

    Reply

Leave a comment