How to Swap Text in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to swap text in excel


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.

Text Swapping with Excel Flash Fill Option

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

Text Swapping with Excel Flash Fill Option

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

Text Swapping with Excel Flash Fill Option


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:
=RIGHT(B5,LEN(B5)-FIND(" ",B5))&" "&LEFT(B5,FIND(" ",B5)-1)

Insert Formula to Swap Text Within One Cell

In this formula, we have used a combination of the RIGHT and LEFT functions mainly. Inside them, we have used the LEN and FIND functions. Then, we concatenate the results with a space between them.

🔎 How Does the Formula Work?

  • LEN(B5)-FIND(” “,B5): This part of the formula subtracts the length of Cell B5 from the length of the found space which results in 4.
  • RIGHT(B5,LEN(B5)-FIND(” “,B5)): This part becomes RIGHT(B5,4). That is why it extracts the four characters from the right of Cell B5. In this case, the result is Mark.
  • LEFT(B5,FIND(” “,B5)-1): In this part, the FIND function returns the position of the space which is 5. So, the formula becomes LEFT(B5,4). So, the formula returns the first 4 characters from the left. And the result is Wood.
  • If we concatenate the results with a space, then the swapped name appears in Cell C5.
  • Secondly, press Enter and drag the Fill Handle down.

Insert Formula to Swap Text Within One Cell

  • 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:
=RIGHT(B5,LEN(B5)-FIND(",",B5))&" "&LEFT(B5,FIND(",",B5)-1)

Insert Formula to Swap Text Within One Cell


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.

Apply Excel VBA to Switch Text in One Cell

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

Apply Excel VBA to Switch Text in One Cell

  • 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.

Apply Excel VBA to Switch Text in One Cell

  • 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.

Apply Excel VBA to Switch Text in One Cell

  • Finally, you will see the swapped names instantly.

Apply Excel VBA to Switch Text in One Cell


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:
=TEXTJOIN("",TRUE,MID(B5,SEQUENCE(,LEN(B5),LEN(B5),-1),1))

Use TEXTJOIN Function to Reverse Text in Excel

In this formula, we have used the MID, SEQUENCE, and LEN functions inside the TEXTJOIN function.

🔎 How Does the Formula Work?

  • MID(B5,SEQUENCE(,LEN(B5),LEN(B5),-1),1): This part of the formula returns an array in the reverse order of Cell B5. The array can be written as below:

{k,r,a,M, ,d,o,o,W}

  • TEXTJOIN(“”,TRUE,MID(B5,SEQUENCE(,LEN(B5),LEN(B5),-1),1)): After finding the reverse array, the TEXTJOIN function joins the elements of the array.
  • After that, press Enter and drag the Fill Handle down to copy the formula.

Use TEXTJOIN Function to Reverse Text in Excel

  • 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.

Switch Contents of Two Adjacent Cells Manually in Excel

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

Switch Contents of Two Adjacent Cells Manually in Excel

  • Finally, release the cursor to swap cell contents.

Switch Contents of Two Adjacent Cells Manually in Excel

  • 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

Swap Text of Non Adjacent Cells Using Excel VBA

  • 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.

Swap Text of Non Adjacent Cells Using Excel VBA

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

Swap Text of Non Adjacent Cells Using Excel VBA

  • 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.

Swap Text of Non Adjacent Cells Using Excel VBA


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo