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.
    Using Flash Fill feature
  2. Press Ctrl + E to get swapped texts.Output after using Flash Fill

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.
    Using VBA to swap text
  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
    End Sub
  4. To run the code, hit the Run button. Or, press the F5 key.
    Inserting code for swapping textAn input box will appear.
  5. In the input box:
    • Select the cell range that you want to swap.
    • Click OK.

    Selecting input range after running VBA

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

Output after running VBA code

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.
    Using formula to swap text

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)

Formula to swap text if words are separated by comma

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))

formula for swapping 3 words

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.

Swapping Two Adjacent Cells Manually

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

Swapping Rnge of Cells Manually

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.Opening VBA window
  2. Click on the Insert tab > Module.Inserting VBA 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.Opening Macros dialog box
  2. Select the code name and press the Run button.Selecting the Macro to swap text

Step 3: Insert Cell Ranges to Swap

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

    Inserting first cell ranges to swap

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

    Inserting second cell ranges to swap

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

Output after swapping non adjacent cell range

Download Practice Workbook

You can download the practice workbook from here.


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

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

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF