How to Swap Text in Excel (3 Easy Methods)

Swapping text in Excel refers to interchanging or exchanging the positions of words, numbers, or phrases within a cell. This action is commonly used to rearrange or reorganize data, making it more meaningful, and enhancing the overall data management.

In this Excel tutorial, you will learn to swap text in Excel using the Flash Fill feature, formula, and VBA code.

Say, one of your colleagues inserted the names of employees with their last names in the beginning. But you want the first and last names in order. The overview image below shows the before-after result of swapping text.

3 Ways to Swap Text in Excel

Swapping text in Excel can be a common task when rearranging data or reorganizing information. Fortunately, several straightforward methods make this process quick and efficient.

Here are the ways 3 easy ways to swap text in Excel:

Using Flash Fill Feature

Flash Fill is a tool that inspects the entered data and fills other data using a pattern. In case of swapping text, you need to type the desired result and Flash Fill will follow the same pattern for other texts.

To swap text using the Flash Fill tool, follow the steps below:

1. Type the texts of a cell in the desired order in the adjacent cell.
   
2. Press Ctrl + E to get swapped texts.

Note: Flash Fill is only available in Excel 2013 and later. The feature can be found in both the Home and Data tab. To access it from the Home tab, go to Editing group>Fill>Flash Fill. You will find the Flash Fill feature from Data Tools group of the Data tab.

Using VBA Macro

Using VBA code, you can make the text-swapping process automated. Moreover, you do not need any helper columns like the previous method.

Follow these steps to swap text using VBA:

1. Go to Developer tab > Code group > Visual Basic. Or, press on ALT+F11.
   
2. Navigate to Insert tab > Module.
3. Copy and paste the following code in the Module:

   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

4. To run the code, hit the Run button. Or, press the F5 key.
   An input box will appear.
5. In the input box:
   • Select the cell range that you want to swap.
   • Click OK.

   

As a result, you will get the text swapped in Excel.

Note: If the Developer tab is missing in Excel, you can display the Developer tab by following the article: How to Display Developer Tab on the Ribbon in Excel

Using Excel Formula

You can also insert a formula to swap texts in Excel. To create the formula, we must combine 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.

Here are the steps to swap texts using the formula in Excel:

1. Select a blank cell.
2. Write the following formula: =RIGHT(B5,LEN(B5)-FIND(" ",B5))&" "&LEFT(B5,FIND(" ",B5)-1)
3. Drag the Fill Handle down.
   

If the words are separated by a comma (,), then you can use the following formula: =RIGHT(B5,LEN(B5)-FIND(",",B5))&" "&LEFT(B5,FIND(",",B5)-1)

To Swap 3 or More Words:

If you have a text string that contains 3 or more words, then, you can use the following formula: =RIGHT(RIGHT(C5,LEN(C5)-LEN(LEFT(C5,FIND(" ",C5)))),LEN(RIGHT(C5,LEN(C5)-LEN(LEFT(C5,FIND(" ",C5)))))-FIND(" ",RIGHT(C5,LEN(C5)-LEN(LEFT(C5,FIND(" ",C5))))))&" "&LEFT(RIGHT(C5,LEN(C5)-LEN(LEFT(C5,FIND(" ",C5)))),FIND(" ",RIGHT(C5,LEN(C5)-LEN(LEFT(C5,FIND(" ",C5)))))-1) & " " & LEFT(C5,FIND(" ",C5))

How to Swap Cell Contents in Excel

If your data looks mixed up or needs a specific order, you can swap cell contents in Excel. This helps in maintaining accurate and orderly information in your Excel worksheet.

Here are 2 methods to swap cells containing text in Excel:

Case 1: Swapping Two Adjacent Cells Manually

To manually swap two adjacent cells, please follow the steps outlined below:

1. Select the cell that you want to swap.
2. Put the cursor on the upper or lower side of the cell till the cursor changes to a four-headed arrow.
3. Now, press the Shift key and drag the cursor to the left side of the cell with which cell to swap.
4. Release the cursor when a green symbol like the letter “I” appears.

Finally, you can see the cell contents are swapped.

Similarly, you can swap the whole range by selecting and then, dragging them to the left while pressing the shift key.

Case 2: Swapping Non-Adjacent Cells with VBA

If the disparity in the dataset is in two non-adjacent cells then you can use VBA to swap them.

Follow the steps below to see how to swap texts of non-adjacent cells in Excel:

Step 1: Insert VBA code

1. Go to the Developer tab > Visual Basic from the Code group.
2. Click on the Insert tab > Module.
3. Type the code below in the Module window:

   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

4. Save the code and close the window.

Step 2: Run VBA Code

1. Go to the Developer tab > Macros.
2. Select the code name and press the Run button.

Step 3: Insert Cell Ranges to Swap

1. In the first input box:
   • Select the range of cells to swap.
   • Click OK.

   

2. In the second input box:
   • Select the range of cells to swap with.
   • Click OK.

   

Finally, you will be able to swap cell contents of non-adjacent cells.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, you learned about 3 different methods to swap text in Excel. Using Flash Fill, a formula combining RIGHT, LEFT, LEN, and FIND functions, and Excel VBA code, you can swap text inside a cell. All these methods can help you to manage your data efficiently. If you have any suggestions or queries, feel free to ask in the comment section below.

Frequently Asked Questions

How to Reverse Text of a Cell in Excel?

You can do it using the following formula with the TEXTJOIN function: =TEXTJOIN("",TRUE,MID(A1,SEQUENCE(,LEN(A1),LEN(A1),-1),1))

How Do You Swap Rows in Excel?

To swap rows in Excel:

1. Select an entire row.
2. Press Alt + Shift + Up or Down arrow keys.

Can I Swap Text Based on a Specific Condition in Excel?

Yes, you can use more advanced techniques like Excel functions or VBA (Visual Basic for Applications) to swap text based on specific conditions.

Related Article

• How to Swap Columns and Rows in Excel
• How to Swap Non-Adjacent Cells in Excel

<< Go Back to Swap Cells | Excel Cells | Learn Excel