Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Swapping Two Strings.
Written by Allen Wyatt (last updated May 12, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
If you do any serious macro programming, there will eventually come a time when you want to swap the values in two strings. In some versions of BASIC, there are commands that handle this. VBA leaves us to our own devices, however. The following technique should do the trick for most people:
TempString = MyString1 MyString1 = MyString2 MyString2 = TempString
When completed, the values in MyString1 and MyString2 have been swapped, and TempString doesn't matter, since it was intended (by this technique) as a temporary variable anyway.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2349) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Swapping Two Strings.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Macros are often used to process the data in a worksheet. If that data includes dates that need to be processed, you'll ...
Discover MoreAddresses used in a formula can be either relative or absolute. If you need to switch between the two types of ...
Discover MoreDoes your macro need to allow the user to specify a particular file name that should be used by the macro? Here's a quick ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-10-31 13:32:31
Rick Rothstein
You can also do the swap without using a temporary variable...
MyString2 = MyString2 & MyString1
MyString1 = Left(MyString2, Len(MyString2)-Len(MyString1))
MyString2 = Mid(MyString2, Len(MyString1) + 1)
Using the temporary variable should be the faster of the two methods, so if you are doing this in a large loop, use that method; however, for one or a small number of swaps, the time difference should be negligible.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments