Use the FIND function to combine text and date - Text Functions - Microsoft Excel Tutorials | Microsoft Excel Tutorials | The World of Tutorials

The following worksheet contains daily tasks in column A and their corresponding dates in column B. The task here is to combine the data and change the format of the dates. Take a closer look at the following screenshot:

The text string XXX has to be replaced by the dates in column B. To do so, the starting position of the text string has to be determined by using the FIND function. The REPLACE function will replace the XXX text string with the date.

FIND(find_text, within_text, start_num)

find_text: Text to find. Wildcard characters are not allowed.
within_text: Text containing find_text.
start_num: Specifies the first character in the search. If omitted, Excel sets start_num to 1.

To combine and format data at the same time:

1. In a worksheet, copy the data shown in Figure 3-14.
2. Select cells C2:C6 and type the following formula: =REPLACE(A2,FIND("XXX",A2,1),3,TEXT(B2,"MM-DD-YYYY")).
3. Press <-Ctrl+Enter->.