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: Comparing Strings.

Comparing Strings

by Allen Wyatt
(last updated July 24, 2015)

It is not uncommon to compare strings in a macro. For instance, you may need to compare what a user typed with some pre-determined value. If you do this directly, you must take into consideration that the user may not have typed his (or her) string in the same way as you expected. Particularly vexing is the fact that the user may have mixed upper and lower case in their response.

The quickest and easiest way around this is to use either the UCase() or LCase() function on their input before you do the comparison. For instance, let's assume you prompt the user for the word "yes" to verify they want an action done. The following code will check the input, regardless of how the user typed it.

If LCase(sUserIn) = "yes" then bDoIt = True

The trick is make sure your test string is either all upper or all lower case, and then convert the user's input to that same case.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2279) 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: Comparing Strings.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Comparing Lists for Duplicates

Do you have two worksheets on which you need to see if there is duplicate information? Here is a couple of quick ways to ...

Discover More

Following a Number with Different Characters

When creating numbered lists, the normal characters that follow the number are a period and a tab. Here's how to force Word ...

Discover More

Inserting a Radical Symbol

The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Automatically Opening Macro Workbooks when Using a Shortcut Key

Click a button on a toolbar and Excel will go so far as to open a another workbook in order to run a macro associated with ...

Discover More

Understanding the While...Wend Structure

Logical structures are important in programming, as they allow you to control how the programming statements are executed. ...

Discover More

Replacing Some Formulas with the Formula Results

Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share