UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Numbers Stored as Text - Text Stored as Numbers    
Numbers Stored as Text - Text Stored as Numbers

Imported data isn’t always pure. Sometimes you will get what look like numbers but they are actually text strings. Sometimes you import data and it is supposed to be strings but those items that look like numbers get imported as numbers.

There are ways to convert them.

Changing a String to a Number

The easiest way to convert a string that looks like a number to a real number is to multiply it by 1.

For example, you might be using the cell to look up a value from a table using VLOOKUP. In that case the formula: =VLOOKUP (A2 * 1, Lookup_Range, Column_Position, False) will work whether A2 is a true number or a string that looks like a number.

The above formula does a line-by-line conversion.

You can also mass convert a range of cells to number using the following “trick.” In a vacant cell, formatted as a number, type in 1. Then copy this cell. Then highlight the range you want to convert and select Paste Special -> Multiply.

There is another sneaky way to convert strings to numbers using an array formula. This would come in handy if you can’t do a line-by-line conversion or are constantly refreshing the data and don’t want to do the manual copy / paste every time.

Suppose the range you want to “convert” is in Cells A2:A10. Then you can enter the following formula in as an array formula: =SUM(A2:A10 * 1). This will get you the correct sum but won’t actually change the cells.

If it is just a sum you want, you can also use =SUMPRODUCT((A2:A10) * 1). However, if you are using other aggregates like AVERAGE, you will have to use the array formula format.

Changing a Number to a String

To convert a number to a string, concatenate the null string to it.

For example, you might be using the cell to look up a value from a table using VLOOKUP. In that case the formula: =VLOOKUP (A2 & “”, Lookup_Range, Column_Position, False) will work. True strings are not affected while numbers are converted to strings.

Sometimes even more is required. Quite often imported strings with leading zeros will get converted to numbers without the leading zeros. For example, Part Number 000147 will come down as simply 147. In this case, simply adding the null string will not work.

In cases like this, you have to force the number into the same format as the string using the TEXT function. =VLOOKUP (TEXT(A2,”00000#”), Lookup_Range, Column_Postion, False). Fortunately, this formula works whether A2 is the number 147 or the real string 00147.

Unfortunately there is no batch conversion using copy / paste that would work. Converting numbers to strings is a line-by-line operation.

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 2,150 times.  This page was last modified 19:23, 29 July 2013 by dflak.   Disclaimers