Sometimes, you may want to combine text and time values in Excel to create a meaningful output. For example, you may want to display a message like “The meeting starts at 10:00 AM” or “The flight departs at 3:15 PM”.
To combine text and time in Excel, you need to use the concatenation operator (&) and the TEXT function. The concatenation operator (&) joins two or more text strings into one. The TEXT function converts a numeric value (such as a time value) into a text string, using a specified format.
Procedures
To combine text and time in Excel, follow these steps:
- Enter the text string in a cell, such as “The meeting starts at ” or “The flight departs at “.
- Enter the time value in another cell, such as 10:00 or 15:15. Make sure the cell is formatted as Time.
- In a third cell, use the concatenation operator (&) to join the text string and the time value. For example, =A1&B1 or =A2&B2.
- To format the time value as text, use the TEXT function inside the concatenation operator. For example, =A1&TEXT(B1,“h:mm AM/PM”) or =A2&TEXT(B2,“h:mm AM/PM”). The TEXT function takes two arguments: the value to format and the format code. The format code specifies how you want the time value to appear as text. For example, “h:mm AM/PM” displays the hour, minute, and AM/PM indicator. You can use other format codes as well, such as “h:mm:ss” for hour, minute, and second, or “hh:mm” for two-digit hour and minute.
- Press Enter to get the result. You should see the text and time combined in one cell, such as “The meeting starts at 10:00 AM” or “The flight departs at 3:15 PM”.
Explanation
To understand how the formula works, let’s look at an example. Suppose you have the following data in Excel:
| A | B | C |
|---|---|---|
| The meeting starts at | 10:00 | |
| The flight departs at | 15:15 |
To combine the text in column A and the time in column B, you can use the following formula in column C:
=C1&TEXT(D1,“h:mm AM/PM”)
This formula does the following:
- It joins the text in C1 (“The meeting starts at “) and the time in D1 (10:00) using the concatenation operator (&).
- It converts the time in D1 (10:00) into a text string using the TEXT function. The TEXT function uses the format code “h:mm AM/PM” to display the time as 10:00 AM.
- It returns the result as “The meeting starts at 10:00 AM”.
Similarly, you can use the following formula in C2 to combine the text in A2 and the time in B2:
=C2&TEXT(D2,“h:mm AM/PM”)
This formula does the following:
- It joins the text in C2 (“The flight departs at “) and the time in D2 (15:15) using the concatenation operator (&).
- It converts the time in D2 (15:15) into a text string using the TEXT function. The TEXT function uses the format code “h:mm AM/PM” to display the time as 3:15 PM.
- It returns the result as “The flight departs at 3:15 PM”.
Scenario and Example
To illustrate how to combine text and time in Excel, let’s consider a scenario where you want to create a schedule for a conference. You have the following information in Excel:
| A | B | C |
|---|---|---|
| Event | Start Time | End Time |
| Registration | 8:00 | 9:00 |
| Opening Remarks | 9:00 | 9:15 |
| Keynote Speech | 9:15 | 10:00 |
| Break | 10:00 | 10:15 |
| Panel Discussion | 10:15 | 11:00 |
| Lunch | 11:00 | 12:00 |
| Workshop | 12:00 | 13:00 |
| Closing Remarks | 13:00 | 13:15 |
You want to create a column that displays the event name and the start and end times in a single cell, such as “Registration: 8:00 AM – 9:00 AM” or “Keynote Speech: 9:15 AM – 10:00 AM”.
To do this, you can use the following formula in column D:
=A2&”: “&TEXT(B2,“h:mm AM/PM”)&” – “&TEXT(C2,“h:mm AM/PM”)
This formula does the following:
- It joins the event name in A2 (such as “Registration”) and a colon and a space (“: “) using the concatenation operator (&).
- It converts the start time in B2 (such as 8:00) into a text string using the TEXT function. The TEXT function uses the format code “h:mm AM/PM” to display the time as 8:00 AM.
- It joins a dash and a space (” – “) using the concatenation operator (&).
- It converts the end time in C2 (such as 9:00) into a text string using the TEXT function. The TEXT function uses the format code “h:mm AM/PM” to display the time as 9:00 AM.
- It returns the result as “Registration: 8:00 AM – 9:00 AM”.
You can copy the formula down to the rest of the column to get the following output:
| A | B | C | D |
|---|---|---|---|
| Event | Start Time | End Time | Schedule |
| Registration | 8:00 | 9:00 | Registration: 8:00 AM – 9:00 AM |
| Opening Remarks | 9:00 | 9:15 | Opening Remarks: 9:00 AM – 9:15 AM |
| Keynote Speech | 9:15 | 10:00 | Keynote Speech: 9:15 AM – 10:00 AM |
| Break | 10:00 | 10:15 | Break: 10:00 AM – 10:15 AM |
| Panel Discussion | 10:15 | 11:00 | Panel Discussion: 10:15 AM – 11:00 AM |
| Lunch | 11:00 | 12:00 | Lunch: 11:00 AM – 12:00 PM |
| Workshop | 12:00 | 13:00 | Workshop: 12:00 PM – 1:00 PM |
| Closing Remarks | 13:00 | 13:15 | Closing Remarks: 1:00 PM – 1:15 PM |
Other Approaches
There are other ways to combine text and time in Excel, such as using the & operator without the TEXT function, or using the CONCATENATE function instead of the & operator. However, these methods may not give you the desired format for the time values, or may require additional steps to format the cells.
For example, if you use the & operator without the TEXT function, such as =A2&”: “&B2&” – “&C2, you may get a result like “Registration: 0.333333333333333 – 0.375”. This is because Excel stores time values as fractions of a day, and displays them as decimals by default. To fix this, you need to format the cells as Time, which may change the appearance of the text as well.
Alternatively, if you use the CONCATENATE function instead of the & operator, such as =CONCATENATE(A2,”: “,TEXT(B2,“h:mm AM/PM”),” – “,TEXT(C2,“h:mm AM/PM”)), you may get the same result as using the & operator with the TEXT function, such as “Registration: 8:00 AM – 9:00 AM”. However, the CONCATENATE function is longer and less intuitive than the & operator, and may not be compatible with some versions of Excel.
