Depending on the
original source of data, numbers can be held in Excel as text or as
numbers. Either way, they generally look the same (especially when left
or right aligned), but Excel treats them differently
A common problem with VLOOKUPs and other functions is when the value you’re looking up is held in Excel as text, and the corresponding value in the lookup table is held as a number (or vice versa). [Note that for VLOOKUP problems , the VlOOKUP assistant may solve your problems .]
Generally, values held in Excel as text have a single quote ‘ in front of them. It is a common misconception that reformatting a numeric cell so that it looks like text will mean that it is treated as text. Unfortunately Excel itself doesn’t help to dispel this myth by stating that “Text format cells are treated as text even when a number is in the cell.”. Choosing Format cells against a selected cell which holds a number , and then choosing the category Text from the Number tab will make the cell look like text, but a Vlookup function still won’t find it if it is looking in a lookup table which holds text values.
The reason is due to the way computers store numbers and characters (text). Computers store everything as numbers. Ultimately they are stored as binary ones and zeros, but in the simplest terms one storage location (one byte) holds a value as two hexadecimal digits with a maximum value of FF, which corresponds to 256. Each character is represented by a number corresponding to that number in a “character map” of 256 different characters. Therefore one character can be stored in one byte of information. Numbers on the other hand, can simply be held as hexadecimal digits, and therefore one byte of information can hold a number between 0 and 255, which looks more like 3 digits to us.
This macros converts text to numbers and vice versa.
The "convert numbers to text and add a leading zero" macro does exactly what it says. Using this is necessary on many occasions - especially where a value is held as text and being looked up as text including the leading zero (e.g. on Telephone numbers, and Tesco store numbers). This macro also handles formula entries, and if the value you're converting is part of a formula, it will add to that formula to include the leading zero.