Do you need to split a column of imported Excel telephone number data into two columns so that you have the area code in one column and the seven-digit phone number in the other column? For example, do you have a column of data in this format:

705-555-1133

that you want to transform into two columns where the first column has

705 in it and the second column has 555-1133 in it?

You can use Power Query in Excel to do this. This post will provide a simple example of this transformation in Power Query.

Let’s suppose we have a text file called Employees.txt that has four columns: FirstName, LastName, Phone, and Salary. We want to import it into an Excel worksheet so that we end up with five columns: FirstName, LastName, AreaCode, Phone, and Salary. Below is a screenshot of what we’re starting with.

In Excel, go to Data, From Text/CSV, navigate to your text file, click the Import button and click the Transform button, and click on the Phone column

Transform, Split Column, By Delimiter.

Success! Below is what we end up with. Perhaps we could rename the two phone columns. You can rename the columns right in Power Query as well, which I would recommend.

Another way to do this is to use formulas in Excel.