How to Swap Text in Excel (4 Easy Methods)

In this article, we will learn to swap text in Excel. Sometimes, an Excel sheet may contain text in the wrong order. To understand them, you need to swap text in Excel. Today, we will use 4 methods. Using these methods, you can easily switch text in Excel. Also, we will discuss the methods to swap the text of cells in this article. So, without further delay, let’s start the discussion.

How to Swap Text in Excel: 4 Easy Methods

To explain the methods, we will use a dataset that contains some names of the employees of a company in the wrong order. For example, the name of the first employee should be “Mark Wood”. But it is stored as “Wood Mark” in Cell B5. Using the methods, we will try to swap the texts to get the names in the correct order. Also, we will demonstrate the way to reverse text strings in Method 4.

1. Text Swapping with Excel Flash Fill Option

In Excel, we can use the “Flash Fill” option to swap text easily. Flash Fill is one of the most effective features of Microsoft Excel. It is a tool that inspects the entered data and fills other data using a pattern. In case of swapping text, we need to type the desired result and Flash Fill will follow the same pattern for other texts. Let’s follow the steps below to see how we can swap texts using the Flash Fill option.

STEPS:

• First of all, select Cell C5 and type the result you want.
• In our case, we have typed “Mark Wood”. It is the way we want to see the names of the range B5:B9.
• Secondly, select the range C5:C9.

• Thirdly, go to the Home tab and click on the Fill option. A drop-down menu will appear.
• Select Flash Fill from there.

• As a result, you will see the swapped names like in the picture below.

2. Insert Formula to Swap Text Within One Cell

We can also insert a formula to swap texts within one cell in Excel. To create the formula, we need to use a combination of the RIGHT, LEFT, LEN, and FIND functions. But this formula has a drawback. It will work correctly only when you have two words to swap. Let’s pay attention to the steps below to learn more.

STEPS:

• Firstly, select Cell C5 and type the formula below:

• Secondly, press Enter and drag the Fill Handle down.

• After dragging the Fill Handle, you will see the swapped names.

• If you have a text string that contains 3 or more words, then, this formula will not work correctly. You can see the result below:

• If the words are separated by a comma (,), then you can use the formula below:

3. Apply Excel VBA to Switch Text in One Cell

We can also apply VBA to switch texts in one cell in Excel. To explain the method, we will use the below dataset. We don’t need any helper columns like the previous two methods.

Let’s observe the steps below to learn the whole technique.

STEPS:

• In the first place, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
• Alternatively, you can press Alt + F11 to open it.

• Secondly, go to the Insert tab in the Visual Basic window and select Module from the drop-down menu.
• A Module window will appear.

• In the Module window, type the code below:

Sub Swap_Text()
Dim xRng As Range, yRng As Range
Dim k As Long
Dim StringTxt As String, StringFs As String
Dim StringLs As String, M As Integer
On Error Resume Next
Set xRng = Application.InputBox(Prompt:="Range:", _
                     Title:="Microsoft Excel", Type:=8)
For Each yRng In xRng
    On Error Resume Next
    StringTxt = yRng.Value
    Trim (StringTxt)
    M = InStr(StringTxt, " ")
    StringLs = Left(StringTxt, M - 1)
    StringFs = Right(StringTxt, Len(StringTxt) - M)
    yRng.Value = StringFs & " " & StringLs
Next
End Sub

• Press Ctrl + S to save the code.
• Now, you can press the F5 key to run it.
• Otherwise, navigate to the Developer tab and select Macros. It will open the Macro window.

• In the Macro window, select the desired code and Run it.

• A message box will appear.
• Select the range where you want to apply the VBA. Here, we have selected the range B5:B9.
• Then, click OK to proceed.

• Finally, you will see the swapped names instantly.

4. Use TEXTJOIN Function to Reverse Text in Excel

In Excel, we can use the TEXTJOIN function to reverse texts. It not only swaps texts but also reverses the text string. For example, you have the name “Wood Mark”. Now, if reverse the text string of this name, then it becomes “kraM dooW”. Here, we will show how we can do this with a formula. So, let’s follow the steps below to learn more.

STEPS:

• Firstly, select Cell C5 and type the formula below:

• After that, press Enter and drag the Fill Handle down to copy the formula.

• Finally, you will see the reverse text strings like the picture below.

How to Swap Text of Cells in Excel

Sometimes, we need to swap cells in Excel. The cell may contain text, numbers, or other symbols. Here, we will demonstrate 2 methods. Using them, you can easily swap cell contents in Excel.

1. Switch Contents of Two Adjacent Cells Manually in Excel

In the first method, we will switch the contents of two adjacent cells manually in Excel. Here, we will try to swap Cell C5 with Cell B5. Let’s follow the steps below to learn the whole method.

STEPS:

• First of all, select Cell C5.
• Then, put the cursor on the upper or lower side of Cell C5 till the cursor changes to a four-headed arrow.

• Now, drag Cell C5 to the left side of Cell B5.
• You will see B5 and a green symbol like the picture below.

• Finally, release the cursor to swap cell contents.

• Moreover, to swap the range B4:B9, select it first and then, repeat the steps above.

• Finally, you will see results like the picture below.

2. Swap Text of Non Adjacent Cells Using Excel VBA

In the previous method, we swapped cell contents between two adjacent cells. To swap non-adjacent cells, we need to use VBA. Let’s follow the steps below to see how we can swap texts of non-adjacent cells in Excel.

STEPS:

• In the beginning, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• Secondly, click on the Insert tab and select Module from the drop-down menu.

• In the Module window, type the code below:

Sub Swap_Two_Cells()
Dim Rg1 As Range, Rg2 As Range
Dim Arr1 As Variant, Arr2 As Variant
xTitleId = "Microsoft Excel"
Set Rg1 = Application.Selection
Set Rg1 = Application.InputBox("Range 1:", xTitleId, Rg1.Address, Type:=8)
Set Rg2 = Application.InputBox("Range 2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
Arr1 = Rg1.Value
Arr2 = Rg2.Value
Rg1.Value = Arr2
Rg2.Value = Arr1
Application.ScreenUpdating = True
End Sub

• Press Ctrl + S to save the code.
• After that, press the F5 key to run it.
• Alternatively, you can go to the Developer tab and select Macros. It will open the Macro window.

• In the Macro window, select the desired code and Run it.

• After running the code, a message box will appear.
• Select the first range of cells you want to swap.
• Click OK to proceed.

• Again, another message box will appear.
• Now, select the range of cells that you want to swap with.
• Click OK to move forward.

• Finally, you will be able to swap cell contents of Cells C5 and C6 with Cells B8 and B9.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, we have demonstrated 4 easy methods to Swap Text in Excel. I hope this article will help you to perform your tasks efficiently. Moreover, we have discussed 2 ways to Swap the Text of Cells in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. If you have any suggestions or queries, feel free to ask in the comment section below.

Related Article

• How to Swap Columns and Rows in Excel