Convert Excel Data Types - from Text to Number/Date (General), vice versa

DigDB

Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Convert Excel Data Types - from Text to Number/Date (General), vice versa

Sometimes we receive data which has numbers or dates shown as Texts. You can tell if you see the numeric values left-aligned in cells. By default, all numeric values should align to the right. This often happens when the Excel data comes from an Access database or enterprise reporting systems. This can cause problem in sorting. For example, if treated as numbers, 8 is sorted before 10; but if treated as texts, then 10 is ahead of 8.

Convert Excel Data Types - Text, Number, Date, General

Practice file - convert-date-type-demo.xls (16k)

Sometimes we also want numbers to be treated as texts. You cannot, for example, add preceding zero to a number if the number is treated as number. Therefore, you cannot add '000' to 98 unless '98' is texts, because the 000 will just get discarded in Excel.

There are manual ways in Excel to do this conversion:

From Text to General - Click into each cell, hit Enter button, the text value will become a general value which is the real number or date.
From General to Text - Append an apostrophe ' to the beginning of the cell value. For example, 78 will become '78 which will force Excel to treat the number as text. Note that the apostrophe will NOT be shown or treated as part of the cell value. But some people don't like to have extra apostrophe in cells.

DigDB allows you to do the conversion by ranges and without using the apostrophe. You have two options:

Select a range or ranges, convert in-place the numbers and dates in the range to text types, or vice versa.

Result is:
Select a single cell in a table's column, invoke the 'Column->Convert->Data Type to->...' command, the entire column will be converted. A new column will be inserted, and the new values will be stored there.

Result is: (a new column will be inserted, and the converted values will be placed there)

Need more?

Browse the complete list of DigDB's powerful features listed on the front page.
If you see errors or things not working, check out our error reference page.

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.