Excel: Merge text with numbers

When you have data in multiple columns, you may want to merge them so the data will occupy only one column. 


In the following example, we have a list of people. In the first column, there are first names, in the second- last names and in the third- ages.


Merging text

Create a new column, so you can place your merged data here. Name the cell E2Full name and then enter into cell E3 the following formula =B3&" "&C3 (this means: merge cell B3 with space and then with cell C3).


Press Ctrl + Enter to stay inside the cell. Move your mouse cursor over the bottom right corner of the cell. Use double-click to AutoFill names.

Suppose that after you created the Full name column, you don’t need the First name and the Last name columns anymore. If you simply delete these columns, a reference error (=#REF!) will appear in the Full name cells. This error appears because these cells are not text values but rather references to columns B (First name) and C (Last name).

What you need to do is change the Full name cells to text values. Select cells from E3 to E6 and copy them Ctrl + C. Right-click on these cells and paste them as Values. 


Select cells from E2 to E6 and move them into a place, where the First name cells are located.


Merging text with numbers

Delete the column Last name. Now you have columns with merged first names and last names. 

Let's create a full sentence like "Full name" is "Age" years old. Enter formula =B3&" is "&C3&" years old." into cell D3.

Use the AutoFill option to fill the rest of the cells.

Download attachment