How to Reverse Text to Columns in Excel: 6 Handy Methods

Method 1 – Utilizing Flash Fill Feature to Reverse Text to Columns in Excel

Steps:

Create a new column at the right of the Last Name column.
Name it as Full Name.

Select cell D5 and write down Harry Albert manually.
It’s his Full Name containing the First and Last Name.

Delect cell D5.
Go to the Home tab.
Click on the Fill drop-down icon on the Editing group.
Select Flash Fill from the options.

There is another way to call the Flash Fill feature. Just see the following steps.

Select cell D5.
Move to the Data tab.
Select Flash Fill icon on the Data Tools group.
Press CTRL+E to do the same task.

For those of you who want to learn about more techniques, there is another one.

Use your mouse to place the cursor on the right-bottom corner of the selected cell D5.
Double-click on it.

The remaining cells get filled with Harry Albert by your previous action.
Click on the Auto Fill Options icon at the end of the cells.
Choose Flash Fill from the options.

Get the Full Names in the remaining cells using any of the three approaches stated above.

Method 2 – Using Ampersand (&) Operator to Reverse Text to Columns in Excel

Steps:

Create a new column Full Name just like Method 1.

Select cell D5 and write down the following formula in the Formula Bar.

=B5&" "&C5

B5 and C5 represent the First Name and Last Name of the first student. We used a blank space between two Ampersand operators. Creates a gap between the two parts of the name.

Press ENTER.

Move the cursor as shown in the image below. It will show the Fill Handle tool.
Double-click on the mouse.

It makes the remaining cells get filled with the results.

Method 3 – Implementing CONCAT Function

Steps:

Select cell D5 and paste the following formula.

=CONCAT(B5," ",C5)

Press the ENTER key.

Use the old CONCATENATE function. The process is entirely similar to the above approach.

Select cell D5 and put the formula below.

=CONCATENATE(B5," ",C5)

Hit the ENTER key.

Method 4 – Employing TEXTJOIN Function to Reverse Text to Columns in Excel

Steps:

Select cell D5 and put the following formula.

=TEXTJOIN(" ",TRUE,B5,C5)

Tap ENTER.

We used the Fill Handle tool to get the other results.

Method 5 – Executing Power Query to Reverse Text to Columns in Excel

Steps:

Select cell B4. You can use any other cell inside the data range.
Jump to the Data tab.
Select From Table/Range on the Get & Transform Data group.

The Create Table dialog box opens.
See that the range of cells gets automatically detected by Excel.
Make sure that the box of My table has headers gets checked.
Click OK.

See the columns open in the Power Query Editor.
Select the two columns using the CTRL key.
Right-click on the column heading area.
Select Merge Columns from the context menu.

The Merge Columns wizard opens.
Choose Space as Separator.
Give a New column name. In this case, we named it as Full Name.
Click OK.

We could successfully merge the two columns.

Go to the Home tab.
Click on the Close & Load drop-down.
Select Close & Load To from the two options.

The Import Data wizard will open.
Select Table under the Select how you want to view that data in your workbook section.
Choose the Existing worksheet under Where do you want to put the data? section.
Give the cell reference of D4 in the input box.
Click OK.

The merged column is now available in our worksheet Power Query.

Do some formatting, and the worksheet will look like the one below.

Method 6 – Assigning VBA Code

Steps:

Go to the Developer tab. If you can’t find it, follow this link to display the Developer tab on the ribbon.
Select Visual Basic on the Code group.
Press ALT+F11 to do the same task.

The Microsoft Visual Basic for Applications window opens.
Move to the Insert tab.
Select Module from the options.

It opens the Code Module.
Write down the following code in the Module.

Public Function Rvrs_Txt_Clmn(rng As Range, Optional S As String = " ") _
As String
Dim trns
trns = Application.WorksheetFunction.Transpose _
(Application.WorksheetFunction.Transpose(rng.Value))
Rvrs_Txt_Clmn = Join(trns, S)
End Function