Convert number to text format in Excel so that it can be included in a sentence.
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
I actually told Gavin this morning that I want to send you an e-mail. It was excellent. I have personally asked most of the people who were there what they thought about it, and everybody was really positive and confirmed that they learned a lot.– Melco Conveyor Equipment (Pty) Ltd