You have a sum in Excel. You want to add some text to the sum. Maybe something like "Please remit $" before the number. This video shows two methods. The first uses Concatenation. The second uses a custom number format, with different text showing for positive, negative, and zero. Also in this video: using F4 to repeat a command.
Common Search Terms answered by this video:
how to concatenate a number with text in Excel
how to add text to sum in Excel.
how to join text and a number in Excel.
Table of Contents
(0:00) Concatenate text with SUM
(0:26) Custom number format to add text before number
(1:40) Wrap-up
Common Search Terms answered by this video:
how to concatenate a number with text in Excel
how to add text to sum in Excel.
how to join text and a number in Excel.
Table of Contents
(0:00) Concatenate text with SUM
(0:26) Custom number format to add text before number
(1:40) Wrap-up
Transcript of the video:
Add text to the sum in Excel. How to add text to the sum in Excel.
I have two ways to do this. So we have a sum function down here. If you just want to put some text in the result, in quotes, the total is, close quote, and then an ampersand. That concatenates those two things together.
And even though these numbers change, the total update.
But there's a sneakier way, using custom number formatting.
In a custom number format, you can have four zones, something for positive, a semicolon, something for negative, another semicolon, what to do if it's zero, a semicolon and text.
Well, we don't expect text in any of these sums, so you just put an @ sign out there to say if there is text, leave it as text.
But for positive we're going to say please remit dollar sign in quotes with a zero.
For negative, no payment due, period, credit balance, dollar sign, that goes in quotes with a zero.
And for actual zeros, we're not even going to show the number.
We're just going to put the words, no balance due.
I'm going to copy this whole thing to the clipboard and we're going to come into Control-1.
Choose custom, and in the Type box, paste that whole thing. Click Okay.
Now I need to repeat that that in two more cells. So I click there, press F4 to repeat, click there.
F4 to repeat. This one needs to be wider.
That's pretty cool.
So now if these numbers change, 2,500, the amount changes here.
If we have a larger negative balance, it shows up and here, as long as it stays at zero, it says, "No balance due".
It's a great question.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
I have two ways to do this. So we have a sum function down here. If you just want to put some text in the result, in quotes, the total is, close quote, and then an ampersand. That concatenates those two things together.
And even though these numbers change, the total update.
But there's a sneakier way, using custom number formatting.
In a custom number format, you can have four zones, something for positive, a semicolon, something for negative, another semicolon, what to do if it's zero, a semicolon and text.
Well, we don't expect text in any of these sums, so you just put an @ sign out there to say if there is text, leave it as text.
But for positive we're going to say please remit dollar sign in quotes with a zero.
For negative, no payment due, period, credit balance, dollar sign, that goes in quotes with a zero.
And for actual zeros, we're not even going to show the number.
We're just going to put the words, no balance due.
I'm going to copy this whole thing to the clipboard and we're going to come into Control-1.
Choose custom, and in the Type box, paste that whole thing. Click Okay.
Now I need to repeat that that in two more cells. So I click there, press F4 to repeat, click there.
F4 to repeat. This one needs to be wider.
That's pretty cool.
So now if these numbers change, 2,500, the amount changes here.
If we have a larger negative balance, it shows up and here, as long as it stays at zero, it says, "No balance due".
It's a great question.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
