How to Swap Text in Excel

With Text Toolkit for Microsoft Excel

With the Text Toolkit for Excel, you can easily swap text in the selected cells. In this manual, find out how to use the Swap Text utility.

  1. To run Text Toolkit in Excel, go to the Home tab and click the Text Toolkit icon:
    This is the Text Toolkit icon.
  2. Select Swap Text:

    Select Swap Text.

  3. You will see the Swap Text pane. Select your options and click the Swap button:
    The Swap Text pane.
  1. Select the range of cells where you want to swap text.
  2. Type in the separator by which your text will be swapped and press Enter.

    For example, if you've got "John Smith" and you want to turn it into "Smith John", your separator is space, so you press the Space key and then Enter. To swap by line break, press Shift + Enter and then Enter.

    Another example, if you've got "Smith,John" and enter comma as a separator, you'll get "John,Smith".

  3. You can check Case-sensitive, and, in this case, if you want to swap text by the "E" letter and enter "e" into the field above, the tool won't swap your characters correctly as it will be searching for "e" only in lower case. If you do not check this box, the case of the character entered as a separator won't matter.
  4. If your data set includes several separators, you'll have to select the number or the separator occurrence and tick From the beginning or From the end.

    For instance, you've got "AB CD EF" in your cell and you want to swap it by a space. If you select Separator occurrence: 1 and tick From the beginning, your data will be swapped according to the first space from the beginning, so it will become "CD EF AB".

    If you enter Separator occurrence: 2 and tick the From the beginning radio button, you'll get "EF AB CD".

  5. If there is a check mark next to Don't change cells with formulas, the add-in won't process cells that contain formulas.
  6. With the Create a backup copy option selected, the Restore button is added and a hidden copy of your sheet will be saved. After the tool has processed your data, the Restore button gets enabled. You can click it and return to the previous version of the processed sheet if necessary.
    Here's the Restore button.

    Tip. To see available backup copies, right-click any sheet tab in your Excel workbook and choose "Unhide".

    Unhide any of the backup sheets that you can see in the list.

    Note. You can delete hidden backups with the Remove all backup sheets option in the More menu. To see it, click the three dots in the lower-right corner of the Text Toolkit pane:

    Find the Remove all backup sheets option in the More menu.

Responses

Monica B Steinmetz says:
February 14, 2023 at 3:22 pm

I have tried to swap names on my cell spread sheet, but I keep getting " No cells have been processed".
I have done every thing correctly. I did the space bar for the separator, case sensitive checked, separator occurrence tried 1 then 2, from the beginning, checked and unchecked don't change cells w/formula and
checked create a backup copy.
After trying and trying I still get the same message every time. What do I need to do to change

John Smith to Smith, John
Thank you,

Hello Monica,

Thank you for your comment.

Please note that the Swat Text tool can swap words within one cell. If the selected cells contain just one word, you will get this message.

If your case is different, then please send us a few screenshots showing what data you have, what result you'd like to get, what options you choose for the add-in. You can email all the details to support@ablebits.com. We'll help you further.

Hye. Good day, sir. May I know where can I find the Text Toolkit button? Because I already search it in options and customize ribbon, but still can't find it.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.