Convert number to text format in Excel so that it can be included in a sentence.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:38
- - Skill Level: 5
- - What's my skill level
Convert number to text format
The TEXT function converts values to text in a specified format.
The easiest explanation of the usefulness of this function is with an example. In this example we have a sales person & the sales they made. We want to put it into a text format, so for example, & I can use the CONCATENATE function. I want to say the salesperson’s name, I’ll put in the text “sold” & a space, that many units, then I say ok. you’ll see that although we’ve got it coming through, it’s pulled through just as a number. So we’re doing the exact same thing again, I can actually just copy down. You’ll see when a percentage comes through it brings it through as a zero point four, so it brings it through as its component number. Using the TEXT function we can actually get this to represent some form of format, so for example this point four becomes a percentage. Let’s just use the TEXT function, let’s go to the FUNCTION WIZARD, find TEXT, click ok. What you’ll see is it asks where is the value you’re looking at, so for this one I’m going to click on 2800, & in the format text, you’ll see that is a number format in text form from the category box. Now what they’re basically saying is using inverted comma’s type in what you want the format to look like, so I’m going to put a dollar sign & I want it to have a zero split with a space, one, two, three, with two decimal points, I close inverted comma’s. When I say ok, you’ll see what it’s done is pulled through the 2800 but pt it into a dollar format. So now we can do the same thing but with a percentage, find text then say ok. The value you want to pull through is over here & the format text, open the inverted comma’s, I’m going to put zero point zero zero percent, close it. When I say ok you’ll see it pulls through the exact same breakdown in the new format, which is 40 percent. Now we can create our functions again, so we’ll use CONCATENATE, say ok. Now what we can do is text one is Buchanan, open inverted comma’s, sold, and now instead of reverting to the number I can refer to the cell that has the TEXT function in it, say units. When I say ok you’ll see it now comes through & includes the formatting, & when we copy down you’ll see that happens with the percentage as well. So just a nice way of formatting numbers so you can use them in your text.