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.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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.
|
|