## Convert Text to Numbers, Numbers to Text

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.