Convert Data with Text to Columns
Text To Columns in the Excel Data menu is an underutilized tool.
Its primary function is parsing data - to separate a single column of text
into multiple columns. For instance, if you download a large list of names in
the format LastName, FirstName but you want first name and last name in separate
columns, you can use Text To Columns (T2C) to parse the downloaded column. In
our case, the column is delimited by a comma: FirstName, LastName. T2C looks for
a comma and puts everything on one side in the first resulting column,
everything up to the next comma in the next column and so on. You can use a
space as a delimiter, a semicolon, the letter Q, any character!
See the video
for more info.
A less common but very useful feature of T2C is to force a data type change.
Sometimes, Excel thinks that a column of numbers is text. Or maybe a column of
dates gets treated like text because it’s in Day Month Year format (as is
common overseas). You can also use text to columns to force Excel to recognize a
column as a particular data type
When using this aspect of T2C, it’s important NOT to parse the data! We don’t
want three different columns with month, day, and year, right? Just one column
with a date in it. It’s important to choose a delimiter that you know is NOT
in the column. That way only one column will result. Choose the column to force
the data type change. Indicate that the data is delimited, then choose a
delimiter NOT in the column like & or @. The next screen will allow you to
indicate what the data is and what format it’s in as shown below.
Notice that
Excel will try to overwrite the existing data-if you want to preserve the
original as a check, change the destination column. See
the video demonstrating this aspect of T2C.
Another nice feature for the accounting department is the conversion of
numbers with trailing minus signs to negative numbers. In some ERP systems,
negative numbers are reported as 10,000- instead of –10,000. It can be a real
headache converting these numbers. In the Advanced… button (shown above) you
can turn on “Trailing minus for negative numbers”. It was
new in Office XP. You might still need to write your a macro (or have I Get It!
Development do it for you!) in earlier versions of Office.