Topic: Data Conversion

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

Automatically Converting to GMT Greenwich Meridian Time, or GMT, is a common reference time used around the world. If you want to convert a time to GMT, this tip provides the information you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Between Buddhist and Gregorian Calendar Systems Using the DATE function to convert a calendar system. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Cells to Proper Case An Excel macro to change cells from uppercase to lowercase. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Forced Text to Numbers Numeric values stored in cells formatted as text can play havoc on some types of formulas. Here’s how to force those cells to be treated as numbers, not text, by Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting From Numbers to Text Excel allows you to store both numeric values and text in cells. If you want to convert a numeric value so it is treated as text by Excel, this is the tip you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Imported Information to Numeric Values When importing data into Excel that was created in a different program, the results you get are dependant on the characteristics of the data. If numeric values are not in a standard form, then they may be treated as text. This tip explains a few ways you can overcome the problem of non-standard formats for incoming numeric data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Phone Numbers Sometimes companies will use mnemonics to quote their phone number, such as 1-800-Weld Inc. In order to know what number to call, you must substitute a number for each letter. This tip provides a macro that can do the conversion for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Radians to Degrees The DEGREES function, explained in this tip, is used to convert radians to degrees. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Text to Values Got some imported data that is being improperly parsed as text? This tip explains how you can convert the text so you can use it properly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Units Using the Analysis ToolPak for the vast conversions the CONVERT function in an Excel worksheet has availabile. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting UNIX Date/Time Stamps Information imported from a UNIX system can have dates and times noted in the native UNIX format. This tip explains how to convert UNIX date/time stamps to something Excel can understand. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Pulling Apart Characters in a Long String Excel includes some powerful ways that you can process text in a worksheet. This tip covers two ways (both with formulas and macros) that you can pull a string apart to get at its individual characters. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Shortening ZIP Codes ZIP Codes come in two varieties: five digit and nine digit. If you want to strip the five-digit ZIP Codes out of a group of nine-digit codes, here are a couple of ways to accomplish the task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

Perplexed by Pivots?

Helpful Links

Great Info!

RSS Feeds