Excel Practice
Lessons Lesson 66

TEXT and VALUE: Converting Between the Two

About this lesson

VALUE turns text that looks like a number into a real number. TEXT turns a number into text, formatted the way you say.

By the end you can

  • turn a number into formatted text with TEXT.
  • turn a number stored as text back into a number with VALUE.

Practises TEXT VALUE ISNUMBER

The idea

They are opposites, and they are used for different reasons. VALUE is repair work. Data imported from another system arrives with its numbers stored as text. Until they are converted they will not add up, sort or match. TEXT is presentation. It turns a number into the exact string you want to show, with the decimal places, the currency or the date format written out.

The trap is what TEXT costs. Whatever goes through it comes out as text, however numeric it looks. So a column of prices formatted with TEXT will not total, will not sort in numeric order, and will not match a price anywhere else. If the value still has to compute, format the cell instead and leave the number alone. Use TEXT when the result is going into a joined string that a person will read, and nowhere else.

The mistake to watch for

Using TEXT on a value that still has to compute. Whatever goes through TEXT comes out as text, however numeric it looks. So a column of prices formatted with TEXT will not total, will not sort in order, and will not match a price anywhere else. Format the cell instead. Keep TEXT for a string a person will read.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

B2 was imported from another system and looks like the number 1450. ISNUMBER answers TRUE when a cell holds a real number. It answers FALSE for anything else, text included.

In D2, type =ISNUMBER(B2) and see which it says.

D2
Row A B C D E F G
1 Field Value Converted
2 Imported quantity 1450
3 Unit price 3.5
4 Order date 45000
5 Percentage 0.184
6
7
8

Every step

  1. 1

    B2 was imported from another system and looks like the number 1450. ISNUMBER answers TRUE when a cell holds a real number. It answers FALSE for anything else, text included. In D2, type =ISNUMBER(B2) and see which it says.

    Hint. One argument, B2.

  2. 2

    Turn it into a real number. In C2, convert the imported quantity in B2.

    Hint. One argument.

  3. 3

    Now the other direction. In C3, show the unit price in B3 as text with exactly two decimal places.

    Hint. The format goes in quotes.

  4. 4

    Formats work on dates too. In C4, show the order date serial in B4 as a day, month and four-digit year, with slashes between.

    Hint. dd, mm and yyyy.

  5. 5

    There is a cost to converting. Read =ISNUMBER(TEXT(B5,"0%")) and say what it returns.

    Hint. What does TEXT always return?