Data Conversion

Tips, Tricks, and Answers

The following articles are available for the 'Data Conversion' topic. Click the article's title (shown in bold) to see the associated article.

Automatically Converting to GMT
You know what time it is, right? (Quick—look at your watch.) What if you want to know what time it is in Greenwich, England? Now you need to know how to convert times from your locality to GMT. This tip shows you how.

Breaking Up Variable-Length Part Numbers
Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a particular length. The task becomes somewhat more difficult if the components can be variable lengths. This tip presents a variety of methods of extracting variable-length component elements of a part number when those components follow a discernable pattern.

Converting an Unsupported Date Format
If you import data produced by a program other than Excel, it might include dates stored in a format not automatically recognized by Excel. This tip shows how you can do the necessary data conversion so that Excel can recognize the dates.

Converting Between Buddhist and Gregorian Calendar Systems
Using the DATE function to convert a calendar system.

Converting Cells to Proper Case
An Excel macro to change cells from uppercase to lowercase.

Converting Codes to Characters
Character codes are the numeric values used, by a computer, to signify various alphanumeric characters. You can use the CHAR worksheet function to discover the character code of any character.

Converting Forced Text to Numbers
If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those cells in some of your formulas. Here's how to force those text-formatted cells back to normal numeric-formatted cells.

Converting From Numbers to Text
If you have a range of numeric values in your worksheet, you may want to change them from numbers to text values. Here's how you can make the switch.

Converting Imported Information to Numeric Values
If the information you import into Excel is treated as text by the program, you may want to convert it to numeric values. This tip explains some great ways you can approach the problem and get the numeric values you need.

Converting Mainframe Date Formats
If you import information from other computer systems into an Excel worksheet, the formats used for that information may not be understood directly by Excel. This tip gives an example of how you can convert a non-standard date format into something Excel can use.

Converting Numbers to Strings
Need to convert a numeric value into a string? It's a snap to do by using the Str function in your macros.

Converting Phone Numbers
Sometimes you receive a phone number that contains alphabetic characters and you need to convert it to a purely numeric phone number. You can easily do that using the macro in this tip.

Converting Radians to Degrees
When applying trigonometry to the values in a worksheet, you may need to convert radians to degrees. This is done by using the DEGREES worksheet function, described in this tip.

Converting Strings to Numbers
When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you need to convert information from one category (data type) to another. Here is how you convert text to numbers.

Converting Text Case
If your worksheets include lots of text, you may need this VBA macro to convert large ranges of text from upper to lower case.

Converting Text to Numbers
Import information from a program external to Excel, and your numbers may be treated as text because of the way that the external program formats them. Here's a quick way to take that text apart so it can be treated like the number it really is.

Converting Text to Values
When you import information originating in a different program, Excel may not do the best job at figuring out what various pieces of that information are used for. Here's a couple of ways to quickly convert imported textual values into the numeric values they should have been all along.

Converting to Hexadecimal
One of the numbering systems often used by programmers is hexadecimal. Excel includes a function (DEC2HEX) that allows you to convert decimal values to hexadecimal.

Converting to Octal
If you need to do some work in the base-8 numbering system (octal), you'll love two worksheet functions provided by Excel for this purpose. These functions allow you to convert values to octal and convert them back again.

Converting Units
Using the Analysis ToolPak for the vast conversions the CONVERT function in an Excel worksheet has availabile.

Converting UNIX Date/Time Stamps
If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to something that Excel can recognize and work with. The conversion is easy, once you understand the way in which the date/time stamps are figured.

Developing Reciprocal Conversion Formulas
Would you like to develop a worksheet that has two cells that will convert whatever is entered in either of the cells? For instance, you might put a measurement in inches in one cell and have it converted into millimeters in the other, and vice versa. This tip explains how you can (and why you must) do this using macros.

Pulling Apart Characters in a Long String
You can easily use formulas to pull apart text stored in a cell. For instance, if you need to pull individual characters from a text string into sequential cells, there are a number of ways you can approach the problem.

Shortening ZIP Codes
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter variety.