This video shows how to combine text and formatted numbers in Excel. Formulas use TEXT function to format numbers as dates, currency, or other number formats. Download the free Excel workbook to follow along with the video.
Watch this short video to see how to combine cells in Excel, with text and number format. The transcript is below the video, and you can download the Excel workbook below, to follow along with the video.
For the written steps and screen shots, and more examples, go to the How to Combine Cells in Excel page.
In this video, you'll see how to combine text and numbers and then format those numbers if necessary, so they show us proper dates or currency or other formats. This is Debra Dalgleish from Contextures.com
On this worksheet, we have a column with text and a column with numbers.
In each text cell, I've ended with a space character. So when I combine the text and the number, there'll be a space character between them.
I'm going to create a very simple formula.
And it nicely combined the text and the number.
Now if I point to the fill handle, and double click to copy that down, all the others are combined, but there's no formatting.
We're going to start with an equal sign and click on the text cell, then our ampersand.
And I want it to show the number, but formatted.
Then how do we want this formatted? We're going to put something inside double quotes. We're going to give it a text string to use, as the guide for formatting.
And it says, Today is Wednesday.
Now in this cell, instead of seeing the weekday name, maybe I'd like to see this day-month format.
And there's the date formatted just the way we have it here
Now, I could copy this down to the next cell, or maybe I'd like to include the year in that one -- d-mmm-yyyy
Click on that cell and press Enter.
Now in this cell, I'd like currency. So in this cell I'll type the currency format: $#,##0
If I copy this down, it should pick up that format.
And for the final one here, we'd like fractions.
And there's our hours with fractions.
Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.
The workbook has the Combine text and formatted number examples from the video, and other TEXT function examples.
The zipped file is in xlsx format, and does not contain any macros.
Last updated: April 11, 2021 2:25 PM