Author: Alan Murray / Source: How-To Geek
Excel’s Text to Columns feature splits text in a cell into multiple columns. This simple task can save a user the heartache of manually separating the text in a cell into several columns.
We’ll start with a simple example of splitting two samples of data into separate columns.
Then, we’ll explore two other uses for this feature that most Excel users are not aware of.Text to Columns with Delimited Text
For the first example, we will use Text to Columns with delimited data. This is the more common scenario for splitting text, so we will start with this.
In the sample data below we have a list of names in a column. We would like to separate the first and last name into different columns.
In this example, we would like the first name to remain in column A for the last name to move to column B. We already have some information in column B (the Department). So we need to insert a column first and give it a header.
Next, select the range of cells containing the names and then click Data > Text to Columns
This opens a wizard in which you’ll perform three steps. Step one is to specify how the content is separated. Delimited means the different pieces of text you want to pull apart are separated by a special character such as space, comma, or slash. That’s the one we’re going to choose here. (We’ll talk about the fixed width option in the next section.)
In the second step, specify the delimiter character. In our simple example data, the first and last names are delimited by a space. So, we’re going to remove the check from the “Tab” and add a check to the “Space” option.
In the final step, we can format the content. For our example, we do not need to apply any formatting, but you could do things like specify whether the data is in the text or date format, and even set it up so that one format converts to another during the process.
We will also leave the destination as $A$2 so that it splits the name from its current position, and moves the last name into column B.
When we click “Finish” on the wizard, Excel separates the first and last names and we now have our new, fully populated Column B.
Text to Columns with Fixed Width Text
In this example, we will split text that has a fixed width. In the data below, we have an invoice code that always begins with two letters followed by a variable number of numeric digits. The two-letter code represents the client and the numeric value after it represents the invoice number. We want to separate the first two characters of the invoice code from the numbers that succeed it and deposit those…
The post How to Use Text to Columns Like an Excel Pro appeared first on FeedBox.