Meraz Al Nahian
This article will show you 7 different easy ways on how to combine name and date in Excel. Sometimes we need to provide people’s names with their birth date. But if we keep their names and date of birth in two different columns, it would be tiresome to type their names and birth dates manually. I hope this article will help you combine name and date in excel in 7 different methods.
We are going to use the following dataset which shows the Name of some people and their consecutive Date of Birth.
Download Practice Workbook
7 Ways to Combine Name and Date in Excel
1. Utilizing Excel CONCATENATE and TEXT Functions to Combine Name and Date
The most common function we use to combine a name and date is the CONCATENATE function and the TEXT function.
Steps:
- To use this function, first, type the following formula in cell D5.
=CONCATENATE(B5, " ",TEXT(C5,"DD/MM/YYYY"))
Here, the CONCATENATE function joins the Name from cell B5 and Date of Birth from cell C5 as a whole in cell D5. We have to put the date as text format for which we used the TEXT function, otherwise we will get a number for the date.
- Now press the ENTER You will see the name Kyle O’Reilley and his birthdate are combined together in cell D5.
- Now use the Fill Handle to AutoFill the lower cells.
Thus you can combine name and date easily using the CONCATENATE function.
Read More: Excel VBA: Combine Date and Time (3 Methods)
2. Using Excel CONCAT & TEXT Functions to Combine Name and Date
We also can use the CONCAT function along with the TEXT function to combine name and date. Let’s discuss the process.
Steps:
- Type the following formula in cell D5.
=CONCAT(B5," ",TEXT(C5,"DD-MM-YYYY"))
Here, the CONCAT function joins the Name from cell B5 and Date of Birth from cell C5 as a whole in cell D5. We have to put the date as text format for which we used the TEXT function, otherwise we will get a number for the date.
- Now press ENTER. You will see the name Kyle O’Reilley and his birth date in cell D5.
- Now use the Fill Handle to AutoFill up the lower cells.
Thus you can combine name and date easily using the CONCAT function.
Note: The difference between CONCAT and CONCATENATE function is that the CONCAT function can merge all strings in a range while the CONCATENATE function can’t. In our dataset, we don’t need to select a range of strings, so we can use both functions to combine name and date perfectly.
3. Combine Name and Date Using Flash Fill Command in Excel
Another effective way to combine name and date in Excel is using the Flash Fill command from Fill ribbon. Let’s discuss the process.
Steps:
- Type the name from B5 (Kyle O’Reilley) and birth date from C5(01-03-1987) manually in cell D5 to create a pattern. As the Flash Fill follows a pattern to Fill cells automatically.
- Now press ENTER.
- Select Home >> Fill >> Flash Fill.
The Flash Fill command follows a pattern. When you type the name and date in cell D5 exactly the same way it’s written in Name and Date of Birth columns (cell B5 and C5 respectively), the Flash Fill command fills the lower cells (D6:D10) with other names and corresponding date of birth.
After that, you will see all the names with corresponding date of birth in your Excel sheet. This is the easiest way to combine name and date.
Similar Readings
- How to Merge Multiple Excel Files into One Sheet (4 Methods)
- Combine Sheets in Excel (6 Easiest Ways)
- How to Combine Rows in Excel (6 Methods)
- Combine Columns into One List in Excel (4 Easy Ways)
- How to Merge Columns in Excel (4 Ways)
4. Applying Ampersand (&) to Combine Name and Date in Excel
Using Ampersand (&) along with the TEXT function is another constructive way to mix up name and date in one cell. I’ll describe the method briefly.
Steps:
- Type the following formula in cell D5.
=B5&" "&TEXT(C5,"DD-MM-YYYY")
Here, the Ampersand (&) works as a bridge for cell B5 and C5. It aggregates the value of cells B5 and C5 cumulatively. And so to convert the value of date to text, we used the TEXT function.
- Now press the ENTER You will see the name and date together in cell D5.
- Now use the Fill Handle to AutoFill the cells D6:D10.
By this way, you can put people’s name and date together in a specific cell.
5. Using TEXTJOIN Function to Unite Name and Date in Excel
We can use the TEXTJOIN function to amalgamate name and date too. Let’s discuss the procedure below.
Steps:
- First, type the following formula in cell D5.
=TEXTJOIN(" ",TRUE,B5,TEXT(C5,"DD-MM-YYYY"))
Here the TEXTJOIN function takes Space (“ ”) as delimiter and then uses TRUE to ignore_empty cells. Next, used the cell references (B5 and C5) as arguments text. We need to convert the data of cell C5 to text value, otherwise we will get a number value instead of date for that used the TEXT function.
- Now press the ENTER button and you will see the name and date from cell B5 and C5 respectively combined together in cell D5.
- Now use the Fill Handle feature to AutoFill cells D6:D7.
By following this approach, you can also put the Name and corresponding Birth Date together in one cell.
6. Applying Power Query from Data Tab
We also can combine Name and Date by using Power Query from the Data tab. However, we need to do some modification to our data table. The new figure of the dataset is given below.
Steps:
- Select the range B4:C10.
- Now select Data >> From Range/Table.
- A dialog box will appear. Just click OK.
- Ensure that My table has headers are selected.
- Eventually, you will see a new window of Power Query Editor containing these two columns. However, you will also see a time 12:00:00 AM by default.
- Now hold the CTRL button and click on the Date of Birth. The 2 columns, both Name and Date of Birth, will be selected.
- From Add Column >> select Merge Columns from the current window.
- A window will appear on the screen. Select Space from the Separator bar and Type Combined Name and Date of Birth as New column name.
- Now click OK. You will see a new column named Combined Name and Date of Birth where the name of these guys and their congruous date of birth are stored.
- To extract these data to the excel spreadsheet, select Home >> Close & Load from Power Query Editor.
Now you will see this information in a new sheet.
Thus you can combine the name and date via table.
7. Use of TODAY Function to Combine Name and Date
Suppose these guys will go to a hotel today and the hotel manager needs to keep the record. He can do this by using the TODAY function along with the Ampersand (&) symbol in Excel.
Steps:
- Type the following formula in cell D5.
=B5&" arrived on "&TEXT(TODAY(),"DD-MM-YYYY")
Here, the TODAY function delivers the date of today being nested in the TEXT function. If we don’t use the TEXT function, we wouldn’t get the date.
- Now press ENTER and you will see the name and corresponding arrival date in cell D5.
- Now use Fill Handle to AutoFill the lower cells.
This procedure combines the name and arrival date of the people in cells D5:D10.
Practice section
Here I’m giving you the dataset. You can practice these methods on your own.
Conclusion
This article emphasizes 7 easy methods on how to combine name and date in cells in Excel. You can use these formulas to combine other text with dates or texts. I hope you will benefit from the procedures I described while solving your own problem. If you have any ideas or questions or feedback, please feel free to leave them in the comment box.
Related Articles
- How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
- How to Combine Graphs in Excel (Step-by-Step Guideline)
- How to Combine Two Graphs in Excel (2 Methods)
- Combine Multiple Excel Files into One Workbook with Separate Sheets
- How to Combine Two Line Graphs in Excel (3 Methods)
- How to Combine Graphs with Different X Axis in Excel
