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. [edit] Changing a String to a NumberThe 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 linebyline 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 linebyline 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. [edit] Changing a Number to a StringTo 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 linebyline operation.

Custom Search

This page has been accessed 2,150 times. This page was last modified 19:23, 29 July 2013 by dflak. Disclaimers 