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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

ISNUMBER answers TRUE for a real number, FALSE otherwise.

In D2, type:

=ISNUMBER(B2)

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

    ISNUMBER answers TRUE for a real number, FALSE otherwise. In D2, type: =ISNUMBER(B2).

    Hint. One argument, B2.

  2. 2

    VALUE turns text that looks like a number into a real one. In C2, convert B2 into a real number, using VALUE.

    Hint. VALUE takes a cell.

  3. 3

    TEXT turns a number into formatted text. In C3, show B3 as text with two decimals. Shape: =TEXT(value,"0.00").

    Hint. The format goes in quotes.

  4. 4

    TEXT formats dates the same way. In C4, show B4 as day, month and four-digit year with slashes, using TEXT.

    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?