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 discernible pattern.

   Converting an Unsupported Date Format
Excel makes it easy to import information created in other programs. Converting the imported data into something you can use can present a challenge. Here's how to make a change from an unsupported date format to one that Excel can work with.

   Converting Between Buddhist and Gregorian Calendar Systems
Converting from one calendar system to another can be a challenge. The key is identifying the differences between the systems and then creating a formula to adjust for those differences.

   Converting Cells to Proper Case
When storing text in a worksheet, you may have a need to change the case of that text so that the initial letter in each work is capitalized. The macros in this tip will help to change the case as you require.

   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
Different industries and different computer systems specify dates in all sorts of strange ways. If you need to convert a date from an off-beat format into one that Excel can understand, you'll like the technique used in this tip.

   Converting Numbers to Strings
When creating macros, it is often necessary to change from one type of data to another. Here's how you can change from a numeric data type to a string data type.

   Converting PDF to Excel
Reports and other formal documents are often distributed in PDF format so that they can be read and printed on a variety of different computer systems. If you need to get information out of a PDF file and into an Excel workbook, the task can be challenging.

   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
Ever notice that if someone types in all CAPS, it appears they are shouting? If your worksheets include lots of text, you may need this handy macro to tame the shouting and convert large ranges of text from uppercase to lowercase.

   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 ASCII Text
When you work with imported or pasted data in an Excel worksheet, you may see some strange looking characters at times. If you want to get rid of those characters, there are a couple of ways you can approach the task.

   Converting to Hexadecimal
Excel allows you to easily convert values from decimal to other numbering systems, such as hexadecimal. This tip explains how to use the DEC2HEX worksheet function.

   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
When converting between measurement systems, you might want to use two cells for each type of measurement. Make a change in one cell and the result appears in the other, and vice-versa. This is, unfortunately, easier said than done.

   Getting Rid of 8-Bit ASCII Characters
When working with data created outside of Excel, you may need to check that data to make sure it contains no unwanted characters. This tip examines how you can look at your data and make sure that unwanted characters (in this case, those that use more than 7 bits) are removed.

   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.

   Working with Imperial Linear Distances
Excel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such as those used for linear distances (miles, feet, etc.). Here's a way you can approach the task of working with such values.