Excel's TEXT Function
Imagine you get a
dump of part numbers and prices from a database. You want to match order
data in an Excel spreadsheet to the data dump using VLOOKUP but nothing
seems to match! What’s going on?
|
PartNo |
Price |
|
OrderNo |
PartNo |
Price |
|
0001 |
$1,200.00 |
|
115707 |
0001 |
#N/A |
|
0054 |
$1,375.00 |
|
115707 |
0054 |
#N/A |
|
0077 |
$1,150.00 |
|
115707 |
0077 |
#N/A |
The Part 'numbers' on the left are really text, but those on the right
are numbers. Even though they look the same, they’re not.
Excel contains a number of features for changing both the look and the
value of cells. It’s important to keep in mind that with Excel, what you see
is NOT what you get.
In the February 2004 newsletter was an article discussing
custom formats. When you change
the format of the cell, the underlying data is undisturbed. For instance,
you may display a date as merely ‘Friday’ but you can still calculate the
number of days between the underlying date and another date.
In this article, we’re going to talk about the TEXT function, which
actually changes the
value of the cell – not just its
appearance. Below you can see a table containing a date and a number and the
resulting text when we apply the format in the adjacent cell.
|
Date |
Format |
Result |
|
| Jan 3,
2007 |
m |
1 |
|
| |
mm |
01 |
|
| |
mmm |
Jan |
|
| |
mmmm |
January |
A typical TEXT formula looks like |
| |
d |
3 |
|
| |
dd |
03 |
=TEXT(A1, “mmm-yy”) |
| |
ddd |
Wed |
|
| |
dddd |
Wednesday |
or |
| |
y |
07 |
|
| |
yy |
07 |
=TEXT(A1, “$##.00”) |
| |
yyy |
2007 |
|
| |
yyyy |
2007 |
|
|
Number |
Format |
Result |
|
| 12.34 |
000 |
012 |
|
| |
#00 |
12 |
|
| |
0.0 |
12.3 |
|
| |
#.# |
12.3 |
|
| |
000.000 |
012.340 |
|
|
|
###.### |
12.34 |
|
In this table we’ve jammed a number of possible format strings together so
that you can see the difference between the format character (like “m”) and
the number of format characters (“mm” vs. “mmmm”).
As we mentioned, the resultant cell is actually text! If you try to
calculate the number of days between Wednesday and another valid date, it
won’t work!
The TEXT function is very useful when you need to solve a problem like the
one above. You’ll need to change back the part number from number to text to
get it to match the part numbers in the catalog. Remember when you get the
format right to use
Copy, Paste Special… Values to get rid of the formulas
and leave only the text.