Contents
There is one extremely useful operator in Visual Basic called Like. It can check the similarity (similarity) of two text strings. Unfortunately, this operator has not found its place in the standard set of functions, but it is very easy to create a user-defined function that will do this.
Open the Visual Basic Editor by pressing ALT+F11 or by selecting from the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new module (menu Insert – Module) and copy the text of this function there:
Function MaskCompare(txt As String, mask As String, CaseSensitive As Boolean) If Not CaseSensitive Then txt = UCase(txt) mask = UCase(mask) End If If txt Like mask Then MaskCompare = True Else MaskCompare = False End If End Function
Close the Visual Basic Editor and return to Excel.
Now through Insert – Function (Insert — Function) in category User Defined (User Defined) you can find our MaskCompare function and use it. The function syntax is as follows:
=MaskCompare(txt; mask; CaseSensitive)
where
txt – any text or cell with text that we check against the mask
mask — a set of characters that are searched for in the text being checked. The set may contain wildcards:
- * – any number of any characters
- ? – any one character
- # – any number (0 – 9)
- [list of characters] – any character from the list
- [!character_list] – all characters except those contained in the list
Case_Sensitive – whether it is necessary to take into account the case when checking:
- 1 – case sensitive
- 0 – character case is not respected
Examples of using the function
The use of special characters in the mask opens up the widest possibilities for the user. Here are some examples of masks:
- # # # – all numbers from 0 to 999
- ????? – all 5 letter words
- m*n – all words starting with “m” and ending with “n”
- *[Austria]* – all words containing at least one of the letters a, o, c, t, p
- *[!abcdefghijklmnopqrstuvwxyz]* – all words NOT containing English letters
- Finding the closest similar word
- What are macros, where to insert macro code in VBA, how to use them