“Text to columns” is a method that we use to separate text into different columns. There are two options available in “text to columns.” One is “delimited,” where we provide a delimiter such as commas, spaces, or hyphens, and the other is “fixed width,” where text is separated into different columns based on predefined widths.
Why Split Text in Excel is Important?
Splitting text in Excel is important for several reasons, and it serves various purposes depending on the specific us. Here are some reasons why splitting text in Excel is important:
- Data Cleaning
- Data Analysis
- Reporting
- Text Extraction
- Data Entry and Validation
How to Do Split Text to Columns in Excel (With Examples)
1. Split Text to Columns Using Delimited Method
This is the easiest way to split text. Suppose you have data in which mentioned Names in one column and you want to split into different columns like first name & last name. Then, follow the below steps:
- Begin by selecting the column containing the text data that you want to split into separate columns.
- Go to the Excel ribbon and navigate to the “Data” tab. Under the “Data Tools” group, locate and click on the “Text to Columns” option.
- New dialog box will open, select the “Delimited” option. Then, click the “Next” button to proceed.
In the next window, you’ll have to choose the delimiters that will separate your text data into columns. Here’s what to do:
- Uncheck all the boxes (this ensures that you have control over the delimiters).
- Select the specific delimiter used in your data. For example, if your data is separated by commas, check the “Comma” option.
- Optionally, you can check the “Treat consecutive delimiters as one” box if your data has multiple consecutive delimiters that you want to treat as a single delimiter.
- In this step, you specify the format for the data in the columns you’re creating. Select “Text” to maintain the data as text if you have alphanumeric or text-based data. And choose the Destination of your text like I choose B2 cell. Click on the “Finish” Button.
- You will now see the text data split into different columns, neatly organized and ready for further analysis or use
2. Split Text to Columns Using Fixed Width
By using the Fixed Width method, we can split text into adjacent columns. This method is also very helpful for data cleaning. For instance, when your entire dataset is in a single column and you want to split it into adjacent columns based on certain criteria, this method is quite useful. Let’s see how to use the Fixed Width method.
I have a dataset that is currently in a single column, and I want to split it into four different columns based on headings. Let’s begin.
- Select the entire data range that you want to split and paste it into a new Excel sheet.
- Navigate to “Data Tab” in Excel ribbon and click on the “Text to Columns” option in the Data Tools group Or you can use the shortcut (ALT + A + E)
- Now Select “Fixed Width” option and click the “Next” button.
- Next you will see vertical lines (also called break lines) adjust these lines according to your data and then proceed by clicking the “Next” button.
- Now choose the column data format and destination cell of your data like I choose B1 and click on the finish button.
- At the end, you see your data has been split into 4 different columns.
Convert Date to Text Using Text to Column
If you don’t prefer using a formula every time to convert a date into text format, you can use the “text to column” feature. Do you want to know the steps for how to do it?
- First, select the column that you want to convert to a text format, like the one shown below:
- Then, go to the “Data” tab and select the “Text to Columns” option.
Note: Shortcut Key of Delimited is (ALT + A + E)
- Next, select the “Delimited” option, and then click the “Next” button.
- Then, a pop-up window will appear. Uncheck all the boxes in this window, and click the “Next” button again.
- After that, you need to choose the “Text” option, select the destination for the converted data, and click the “Finish” button.
- Now, you will see that your date format has been changed to text format.
Summary
“Text to Column” in Excel is a helpful tool. It helps you split text neatly and efficiently. You can use it to organize data into columns or change date formats, making it easier to work with your data. Most commonly, people use it to separate first and last names, but it can be adapted for different data tasks to suit your needs.
Also Read: Row VS Column in Excel Insert a Column in MS Excel Convert Columns to Rows in Excel
