by Allen Wyatt
(last updated August 7, 2020)
Debbie has a worksheet that has many dates in it. To concatenate data with the dates, she has to edit each cell that contains a date and add an apostrophe before the date. She wonders if there is a quick way to do this. She has tried highlighting the row and formatting it as text, but that did not work.
If Debbie wants to automate the manual work she is doing, it could be done with a macro. The following automatically converts the selected cells to text and stuffs the date back into the cell.
Sub DateToText() Dim cell As Range Dim sTemp As String For Each cell In Selection With cell sTemp = .Text .NumberFormat = "@" .Value2 = sTemp End With Next cell End Sub
Note the use of the .Value2 property instead of the .Value property when stuffing the text back into the cell. This is done to ensure that Excel doesn't inadvertently convert the text back into a date value. It shouldn't do it anyway, but the .Value2 property doesn't support the Date data type, so any potential conversion is, by nature, precluded.
You should also note that the macro will convert anything that you have selected, even if it doesn't contain a date. You'll want to make sure that you select only cells containing dates before running it.
Of course, the easiest way to do what Debbie is trying to do is to use the TEXT worksheet function. Suppose, for instance, that cell A1 contains a date. In a different cell you wanted to put the words "The deadline is " before the date. You could do that with a formula like this:
="The deadline is " & TEXT(A1, "mmmm d, yyyy")
Note that the TEXT function requires two parameters: The cell to be converted (A1, in this case) and a pattern to use in the conversion ("mmmm d, yyyy"). If cell A1 contains a date such 6/25/16, then the formula returns the following:
The deadline is June 25, 2016
The pattern you specify, within quotes, follows very closely the patterns you would use in defining a custom format for a cell. For instance, "mmm" returns a three-character month name while "mmmm" returns a fully spelled-out month name. The codes you would use in the pattern have been covered in other ExcelTips, but you can also find a handy summary of codes that conveniently correspond to what Excel uses at Wikipedia:
An interesting point regarding this approach is that it doesn't really matter how the date in cell A1 is formatted. When you use TEXT, it relies on the underlying serial number used by Excel to store the date, and then formats it according to the pattern you specify—independent of any formatting in the cell containing the date.
Of course, if you want to do a straight conversion to text, you don't have to concatenate anything with the date. In this case, you would just use the TEXT function by itself:
=TEXT(A1, "mmmm d, yyyy")
The TEXT function, by nature, returns a strictly text value.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13450) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Getting rid of formatting from a cell or group of cells can be done using several different techniques. This tip ...Discover More
Need a line through the middle of your text? Use strikethrough formatting, which is easy to apply using the Format Cells ...Discover More
If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.