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

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

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

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

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

Tips, Tricks, and Answers

Fast Answers!

Helpful Links

Great Info!

RSS Feeds