How to use the FIND function to combine text and date in Excel

In this article, you will learn how to use the FIND function in Excel to combine text and date values in a single cell. This can be useful when you want to display a date along with some descriptive text, such as “Report due on 12/31/2021” or “Invoice issued on 01/15/2022”.

The FIND function returns the position of a specific character or substring within a text string. For example, =FIND("a","apple") returns 2, because “a” is the second character in “apple”. You can use the FIND function to locate the position of a delimiter, such as a slash (/) or a dash (-), in a date value. Then, you can use the LEFT, RIGHT, or MID functions to extract the year, month, or day parts of the date. Finally, you can use the CONCATENATE function or the ampersand (&) operator to join the text and date parts together.

Example

Suppose you have a list of dates in column A, formatted as mm/dd/yyyy, and you want to combine them with some text in column B, such as “Payment received on” or “Order shipped on”. You can use the following formula in column C to achieve this:

=B2&" "&TEXT(A2,"mm/dd/yyyy")

This formula works as follows:

• The & operator joins the text in B2 with a space and the date in A2.
• The TEXT function converts the date in A2 to a text value, using the format “mm/dd/yyyy”. This ensures that the date is displayed correctly, regardless of the regional settings of your Excel application.
• The result is a text value that combines the text and date in a single cell.

Here is a sample table that shows the formula and the result:

Alternative methods

There are other ways to combine text and date in Excel, depending on your preferences and needs. Here are some alternative methods:

• You can use the DATEVALUE function to convert a text value that represents a date to a serial number that Excel recognizes as a date. For example, =DATEVALUE("12/31/2021") returns 44532, which is the serial number for December 31, 2021. Then, you can use the TEXT function to format the date as you like. For example, =TEXT(DATEVALUE("12/31/2021"),"mm/dd/yyyy") returns “12/31/2021” as a text value.
• You can use the DATE function to create a date value from the year, month, and day parts. For example, =DATE(2021,12,31) returns 44532, which is the serial number for December 31, 2021. Then, you can use the TEXT function to format the date as you like. For example, =TEXT(DATE(2021,12,31),"mm/dd/yyyy") returns “12/31/2021” as a text value.
• You can use the FIND function to locate the position of a delimiter, such as a slash (/) or a dash (-), in a date value. Then, you can use the LEFT, RIGHT, or MID functions to extract the year, month, or day parts of the date. For example, =LEFT(A2,FIND("/",A2)-1) returns 12, which is the month part of the date in A2. Then, you can use the CONCATENATE function or the & operator to join the text and date parts together. For example, =B2&" "&LEFT(A2,FIND("/",A2)-1)&"/"&MID(A2,FIND("/",A2)+1,2)&"/"&RIGHT(A2,4) returns “Report due on 12/31/2021” as a text value.