Excel is a powerful tool that helps manage and format data, but what happens when your data isn’t already in neat Excel columns? That’s where Text to Columns in Microsoft Excel becomes a valuable feature. In this post, you’ll learn how to take unformatted data from other applications—like Word, Notepad, or even a text file—and break it down into columns directly in Excel.

Using Text to Columns can save you from copy-pasting and adjusting manually. Instead, you can auto-format that data quickly based on delimiters or fixed positions. Let’s get into the steps!

What is Text to Columns?

Text to Columns is an Excel tool that allows you to split data into separate columns. You can split this data based on specific characters, like commas, spaces, or tabs, or you can manually tell Excel where to split things up. It’s particularly useful when you have data from a Word document, Notepad, or other formats and want to pull that data neatly into Excel without re-typing everything.

Let’s walk through two simple scenarios:

  • Splitting data using delimited characters like commas or spaces.
  • Splitting data by defining fixed-width positions.

How to Use Text to Columns in Excel with Delimiters

A delimiter is just a character (like a comma or space) that separates various columns of data. In this case, I’ll show you how to split data that’s separated by commas.

  1. Open your data source. This could be a Word document, Notepad, or anything else. Let’s say you have a list of fruits, quantities, and prices, separated by commas: Apples,10,$5 Oranges,20,$10 Bananas,15,$7
  2. Copy the data (Ctrl + C) and paste it into Excel (Ctrl + V). When you paste it, the data will appear in a single column, likely Column A.
  3. Highlight the pasted data. Now select the entire column or range of cells where the data was pasted.
  4. Go to the Data tab in Excel’s toolbar. Under the “Data Tools” group, click Text to Columns.
  5. In the Text to Column wizard, select Delimited and hit Next.
  6. Now, specify the delimiter. Since our data is separated by commas, check the “Comma” box and uncheck all others, then click Next.
  7. Review the preview. It should show you “Apples” in Column A, “10” in Column B, and “$5” in Column C.
  8. Choose Finish. Voilà! Your data is now neatly split into different columns, and you can manipulate that data like you would if you had entered it manually.

Let’s say your numerical columns contain dollar amounts that were formatted as general text. To format those cells as currency:

  1. Highlight the column with dollar amounts.
  2. Right-click, select Format Cells, choose Currency, and click OK.

Now all amounts are properly formatted in Excel.

RELATED TOPIC: How to Convert a Picture into an Editable Table in Excel

How to Use Text to Columns with Fixed Width

But what if your data isn’t separated by a character? No problem—you can use fixed width to split it up.

Let’s say you have a product code, followed by a series of digits, like this:

AGG123456  
AGG789012
AGG345678

To split this up, follow these steps:

  1. Copy and paste the data into Excel, just like before.
  2. Highlight the column of data and once again go to Data > Text to Columns. This time, select Fixed Width and click Next.
  3. Excel will display a ruler-like interface where you can add breaks manually. Click between where the letters “AGG” end and the numbers begin. This creates a split. Now the first part of the code (“AGG”) will go into Column A, and the numbers into Column B.
  4. Hit Next, then click Finish. You’ll now see “AGG” in one column and the numbers in the column next to it.

Want even more control? Let’s say you only want the “AGG” part and the first four digits of the number. You can repeat the steps, but this time after setting your delimiter, select the box that says Do Not Import next to the unwanted column, and click Finish.

That gives you even more flexibility to choose exactly which parts of the data you want to keep.

Common Uses for Text to Columns in Microsoft Excel

You can apply Text to Columns in Microsoft Excel in many real-world situations:

  • Customer lists: If you have full names in one column, you can split them into first and last names for easier sorting.
  • Financial data: Use it to organize large CSV exports that come in unformatted.
  • Product codes: Split product codes into multiple columns when you need only parts of the code, or group them differently.
  • Address data: Divide street addresses, cities, and postal codes into separate columns.

RELATED TOPIC: How to Create Interactive Maps from Excel Data

Conclusion

The Text to Columns in Microsoft Excel feature in Excel is far more than a simple tool—it’s a time-saver that can handle data, so you don’t have to manually format or retype text. Whether you’re working with delimited or fixed-width inputs, this feature helps you organize your data into usable columns quickly.

Next time you’re handling large, unformatted data sets, remember that there’s no need to do the heavy lifting manually. Text to Columns takes care of it for you.

Make sure to subscribe to our YouTube channel for more Excel tips and tutorials. Happy data organizing!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR