قالب وردپرس درنا توس
Home / Tips and Tricks / How To Use Text in Columns Like an Excel Pro

How To Use Text in Columns Like an Excel Pro



  Excel logo

Excel's "Text in columns" feature splits the text in a cell into multiple columns. This simple task can save a user the trouble of manually dividing the text in a cell into multiple columns.

We begin with a simple example in which two data samples are split into separate columns. Here are two other uses for this feature that most Excel users are not familiar with.

Text in columns with limited text

In the first example, we will use text in columns with separated data. This is the more common scenario for splitting text. We start with it.

In the following sample data, we have a list of names in a column. We want to divide the first and last names into different columns.

 List of names to be separated by text in columns

In this example, we want to leave the first name in column A. The last name is moved to column B. Column B (Department) already contains some information. So we have to insert a column first and give it a header.

 Column written for surnames

Next, select the range of cells that contains the names and click Data> Column Text [19659003]

This will opens a wizard in which you perform three steps. The first step is to specify how the content is separated. Limited means that the different parts of the text that you want to expand are separated by a special character, such as space, comma, or slash. That is what we will choose here. (We'll talk about the fixed-width option in the next section.)

 Step 1 of the wizard

In the second step, specify the delimiter. In our simple sample data, first and last names are delimited by a space. Therefore, we remove the hook from the "Tab" and add a check mark to the option "Space".

 Step 2 of the Column Wizard Text

In the last step, we can format the content. In our example, we do not have to apply formatting, but you can specify, for example, whether the data is in text or date format, and even set it up to convert one format to another during the process.

We will also leave the target as $ A $ 2 so that the name is separated from its current position and the last name is moved to column B.

 Step 3 of the Text Column Wizard

When we click Finish in the wizard, Excel separates the first and last name, and we now have our new, fully populated column B.

 Split names into different columns

Split text into fixed-text columns

In this example, we divide fixed-width text. In the following data, we have a billing code that always starts with two letters, followed by a variable number of numeric digits. The two-letter code stands for the customer and the numeric value for the invoice number. We would like to separate the first two characters of the invoice code from the following numbers and store these values ​​in the columns "Customer" and "Invoice" (columns B and C). We also want to keep the full billing code in column A.

 Example data for fixed-width text

Since the billing code always consists of two characters, it has a fixed width.

First, select the range of cells that contains the text you want to split, and then click Data> Text in Columns.

 Button

On the first page of the Select fixed width option, click Next.

 Split text with fixed width

On the next page, you must specify the position (s) in the column to divide the content. We can do this by clicking in the provided preview area.

Note: Text to Columns sometimes contains one or more pauses. This can save you some time, but keep an eye on it. The suggestions are not always correct.

In the Data Preview area, click where you want to insert the fraction, and then click Next.

 Insert a Column Break in "Text in Columns" [19659003] In the last step in the Target box, type cell B2 (= $ B $ 2), and then click Finish.

 Set a destination for split cells

The invoice numbers are successfully separated in columns B and C. The original data remains in column A.

 Columned text fixed-width

We have now dealt with splitting content using separators and fixed widths. We also tried to split text on the spot and divide it into different parts of a worksheet. Now let's look at two special special applications of text in columns:

Converting US dates into European format

A fantastic use of text in columns is converting date formats. Example: Converting a US date format to a European format or vice versa.

I live in the UK. So when I import data into an Excel spreadsheet, sometimes they are saved as text. This is because the source data is from the US and the date formats do not match the regional settings configured in my Excel installation.

So the text in columns is the salvation to get these converted. Here are some US-style data that my version of Excel did not understand:

 US date formats to convert

First, we will select the cell range that contains the dates to convert. Then click on data> text in columns.

 Button

On the first page of the wizard, we leave it as a boundary, and in the second step, we'll remove all the clipping options, because we really do not want to share any content.

 All delimiter options not selected

Select Date on the last page and use the list to specify the date format of the data received. In this example, I choose MDY – the format normally used in the US.

 Selecting the MDY format for dates

After you click Finish, the dates are converted successfully. More Analysis.

 Converting US dates into UK format

Converting international number formats

In addition to the ability to convert various date formats, text in columns can also be used to convert international number formats.

Here in the UK a decimal point is used in number formats. For example, the number 1,064.34 is just over one thousand.

In many countries a decimal comma is used instead. This number would be misinterpreted by Excel and saved as text. They would represent the number as 1,064.34.

If you work with international number formats in Excel, our good text "text in columns" can help us convert those values.

In the following example, I have a list of numbers formatted with a decimal point. Therefore they did not recognize my regional settings in Excel.

 European Number Formats for Conversion

This operation is almost identical to that used to convert dates. Select the value range, go to Data> Text in columns, select the option with separators, and remove all separators. In the last step of the wizard, this time we select the "General" option and then click on the "Advanced" button.

 Advanced options in step 3 of the wizard

In the window that opens, enter the character that you want to use in the Thousands separators and Decimal separators fields. Click "OK" and then "Finish" when returning to the wizard.

 Specifying the decimal and thousands separator

The values ​​are converted and recognized as numbers for further calculation and analysis

 Numbers converted by text into columns


Text in columns is more powerful than people realize. Its classic use of separating content into different columns is incredibly useful. Especially when working with data we get from others. The lesser-known ways to convert date and international number formats are magical.


Source link