In this Excel file, we have the first name, last name, and member number in three separate columns. We want to merge these columns into one column called full name. We also want to calculate the age of the customer by the birthday field.


2. The solution

To do this, we first click and highlight the first name column. Holding the control key, we select the last name and then the member number. This will select the three columns. Right-click on one of the column headings and choose Merge Columns. Choose the space as the Separator, and the Full name as the new column name.

Right-click on the Merged column and rename it to Full Name. Now you have the Full Name column containing the correct data.
In the table, there is a column called Birthday, which is displayed as a number. This column can be easily transformed to a Date as shown below.

To work out the age, click on the birthday column, go to Add column, and in the date drop-down, select age. A new column appears called age, but it is displayed in days. To convert it to years, right-click on the Age column, select transform, and total years. Round the decimal to the nearest year by right-click on the age column, and choose to transform, round, round down. Now we have the age calculated for each row of data.

The Data School
Author: The Data School