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
The DEGREES function, explained in this tip, is used to convert radians to degrees.

Converting Strings to Numbers
When creating a macro, you'll often need to convert strings to numbers. For instance, you might have some user input that needs to be converted. This is done using the Val function, as described in this tip.

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
If you import information into Excel that was generated by a different program, the numeric formats used by that program may not be automatically recognized by Excel. In that case, you might want to use a macro, such as the one described in this tip, to do the conversion for you so that you can work with the numbers properly.

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
One of the numbering systems often used by programmers and electrical engineers is base 8, or octal. Excel allows you to convert decimal values to and from octal, using the functions highlighted in this tip.

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.