Microsoft Word has it's standard wildcards plus if you tick Use Wildcards it is a Regex engine (plus find words that sound the same, and words with the same root). So use Word rather than Vbscript's RegEx. Just record a Find and Replace in Word and you'll get most of the program written for you that you'll just need to adapt.
The tutorial explains the syntax of the Excel FIND and SEARCH functions and provides formula examples of advanced non-trivial uses.
In the last article, we covered the basics of the Excel Find and Replace dialog. In many situations, however, you may want Excel to find and extract data from other cells automatically based on your criteria. So, let's have a closer look at what the Excel search functions have to offer.
The FIND function in Excel is used to return the position of a specific character or substring within a text string.
The syntax of the Excel Find function is as follows:
The first 2 arguments are required, the last one is optional.
If the FIND function does not find the find_text character(s), a #VALUE! error is returned.
For example, the formula
=FIND('d', 'find') returns 4 because 'd' is the 4th letter in the word 'find'. The formula
=FIND('a', 'find') returns an error because there is no 'a' in 'find'.
To correctly use a FIND formula in Excel, keep in mind the following simple facts:
The SEARCH function in Excel is very similar to FIND in that it also returns the location of a substring in a text string. Is syntax and arguments are akin to those of FIND:
Unlike FIND, the SEARCH function is case-insensitive and it allows using the wildcard characters, as demonstrated in the following example.
And here's a couple of basic Excel SEARCH formulas:
=SEARCH('market', 'supermarket') returns 6 because the substring 'market' begins at the 6th character of the word 'supermarket'.
You can even email photos and documents to your friends.– Change network settingsYou can change network settings of your FileHub wireless network, and even connect your FileHub to existing wireless internet.– Other features. Rav filehub app for mac. Up to 5 devices can connect to RAVPower FileHub at the same time, making it an ideal for sharing media and work files between your friends.– File managementRAVPower FileHub lets you easily manage and transfer files between your devices and the USB storage attached to RAVPower FileHub. You can upload files to the USB storage and make it available for other devices to download.– Browser photos and videosYou can browse and preview photos and videos on USB storage attached to RAVPower FileHub or files that have already been downloaded to your device.
=SEARCH('e', 'Excel') returns 1 because 'e' is the first character in the word 'Excel', ignoring the case.
Like FIND, Excel's SEARCH function returns the #VALUE! error if:
Further on in this tutorial, you will find a few more meaningful formula examples that demonstrate how to use SEARCH function in Excel worksheets.
As already mentioned, the FIND and SEARCH functions in Excel are very much alike in terms of syntax and uses. However, they do have a couple of differences.
The most essential difference between the Excel SEARCH and FIND functions is that SEARCH is case-insensitive, while FIND is case-sensitive.
For example, SEARCH('e', 'Excel') returns 1 because it ignores the case of 'E', while FIND('e', 'Excel') returns 4 because it minds the case.
Unlike FIND, the Excel SEARCH function allows using wildcards in the find_text argument:
To see how it works on real data, consider the following example:
As you see in the screenshot above, the formula SEARCH('function*2013', A2) returns the position of the first character ('f') in the substring if the text string referred to in the within_text argument contains both 'function' and '2013', no matter how many other characters there are in between.
In practice, the Excel FIND and SEARCH functions are rarely used on their own. Typically, you would utilize them in combination with other functions such as MID, LEFT or RIGHT, and the following formula examples demonstrate some real-life uses.
This example shows how you can find and extract all characters in a text string to the left or to the right of a specific character. To make things easier to understand, consider the following example.
Supposing you have a column of names (column A) and you want to pull the First name and Last name into separate columns.
To get the first name, you can use FIND (or SEARCH) in conjunction with the LEFT function:
=LEFT(A2, FIND(' ', A2)-1)
=LEFT(A2, SEARCH(' ', A2)-1)
As you probably know, the Excel LEFT function returns the specified number of left-most characters in a string. And you use the FIND function to determine the position of a space (' ') to let the LEFT function know how many characters to extract. At that, you subtract 1 from the space's position because you don't want the returned value to include the space.
To extract the last name, use the combination of the RIGHT, FIND / SEARCH and LEN functions. The LEN function is needed to get the total number of characters in the string, from which you subtract the position of the space:
Download telegram for mac os x 10.10.5.
The following screenshot demonstrates the result:
For more complex scenarios, such as extracting a middle name or splitting names with suffixes, please see How to split cells in Excel using formulas.
Supposing you have some text strings in column A, say a list of SKUs, and you want to find the position of the second dash (-) in a string. The following formula works a treat:
=FIND('-', A2, FIND('-',A2)+1)
The first two arguments are easy to interpret: locate a dash ('-') in cell A2. In the third argument (start_num), you embed another FIND function that tells Excel to start searching beginning with the character that comes right after the 1st occurrence of dash (FIND('-',A2)+1).
To return the position of the 3rd occurrence, you embed the above formula in the start_num argument of another FIND function and add 2 to the returned value:
=FIND('-',A2, FIND('-', A2, FIND('-',A2)+1) +2)
Another and probably a simpler way of finding the Nth occurrence of a given character is using the Excel FIND function in combination with CHAR and SUBSTITUTE:
Where '-' is the character in question and '3' is the Nth occurrence you want to find.
In the above formula, the SUBSTITUTE function replaces the 3rd occurrence of dash ('-') with CHAR(1), which is the unprintable 'Start of Heading' character in the ASCII system. Instead of CHAR(1) you can use any other unprintable character from 1 to 31. And then, the FIND function returns the position of that character in the text string. So, the general formula is as follows:
At first sight, it may seem that the above formulas have little practical value, but the next example will show how useful they are in solving real tasks.
To locate a substring of a given length within any text string, use Excel FIND or Excel SEARCH in combination with the MID function. The following example demonstrates how you can use such formulas in practice.
In our list of SKUs, supposing you want to find the first 3 characters following the first dash and pull them in another column.
If the group of characters preceding the first dash always contains the same number of items (e.g. 2 chars) this would be a trivial task. You could use the MID function to return 3 characters from a string, starting at position 4 (skipping the first 2 characters and a dash):
=MID(A2, 4, 3)
Translated into English, the formula says: 'Look in cell A2, begin extracting from character 4, and return 3 characters'.
However, in real-life worksheets, the substring you need to extract could start anywhere within the text string. In our example, you may not know how many characters precede the first dash. To cope with this challenge, use the FIND function to determine the starting point of the substring that you want to retrieve.
The FIND formula to return the position of the 1st dash is as follows:
Because you want to start with the character that follows the dash, add 1 to the returned value and embed the above function in the second argument (start_num) of the MID function:
=MID(A2, FIND('-',A2)+1, 3)
In this scenario, the Excel SEARCH function works equally well:
=MID(A2, SEARCH('-',A2)+1, 3)
It's great, but what if the group of chars following the first dash contains a different number of characters? Hmm.. this might be a problem:
As you see in the above screenshot, the formula works perfectly for rows 1 and 2. In rows 4 and 5, the second group contains 4 characters, but only the first 3 chars are returned. In rows 6 and 7, there are only 2 characters in the second group, and therefore our Excel Search formula returns a dash following them.
If you wanted to return all chars between the 1st and 2nd occurrences of a certain character (dash in this example), how would you proceed? Here is the answer:
=MID(A2, FIND('-',A2)+1, FIND('-', A2, FIND('-',A2)+1) - FIND('-',A2)-1)
For better understanding of this MID formula, let's examine its arguments one by one:
In a similar fashion, you can return 3 characters after the 2nd dash:
=MID(A2, FIND('-',A2, FIND('-', A2, FIND('-',A2)+1) +2), 3)
Or, extract all the characters between the 2nd and 3rd dashes:
=MID(A2, FIND('-', A2, FIND('-',A2)+1)+1, FIND('-',A2, FIND('-', A2, FIND('-',A2)+1) +2) - FIND('-', A2, FIND('-',A2)+1)-1)
Supposing you have some long text string in column A and you want to find and extract only the text enclosed in (parentheses).
To do this, you would need the MID function to return the desired number of characters from a string, and either Excel FIND or SEARCH function to determine where to start and how many characters to extract.
The logic of this formula is similar to the ones we discussed in the previous example. And again, the most complex part is the last argument that tells the formula how many characters to return. That pretty long expression in the num_chars argument does the following:
Naturally, nothing prevents you from using the Excel FIND function instead of SEARCH, because case-sensitivity or case-insensitivity makes no difference in this example.
Hopefully, this tutorial has shed some light on how to use Search and Find functions in Excel. In the next tutorial, we are going to closely examine the Replace functions, so please stay tuned. And in the meanwhile, you may want to download the Sample FIND and SEARCH worksheet to reverse engineer the formulas for better understanding. Thank you for reading!
VBA Dir is fixed now in the latest updates if you use O365, you can use Dir now to loop through files in a folder and use wildcards to filter to only loop through the files you want. See the basic example below that create a new file with a list of all the files in the folder that you select when you run the code. See also the other option with macscript below this example.
Dir on a Mac in the first versions of Excel 2016 and 2011 is not working the same as in Excel for Windows, you can not use a Filter and wildcards like in Excel for Windows like this to get all Excel files.
FilesInPath = Dir(folderpath & '*.xl*')
Or this to get only xlsm files
FilesInPath = Dir(folderpath & '*.xlsm')
With Dir in Windows you can also say that you want all xls files that contains or start with or Ends with a certain text. But this all is not working on a Mac, so I looked for a solution that I can use on my Mac.
In the Workbook that you can download you can find a macro that will add a list on the worksheet of all the files that match the conditions. To make it easy I use cell references in the function call in the workbook.
In the macro we call the GetFilesOnMacWithOrWithoutSubfolders function like this to fill the MyFiles string.
Call GetFilesOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=0, FileNameFilterStr:='SearchString')
This are the four arguments that you can set in the function call :
'Level : 1= Only the files in the folder, 2 to ? levels of subfolders
'ExtChoice :0=(xls xlsx xlsm xlsb), 1=xls , 2=xlsx, 3=xlsm, 4=xlsb, 5=csv, 6=txt, 7=all files, 8=(xlsx xlsm xlsb), 9=(csv txt)
'FileFilterOption : 0=No Filter, 1=Begins, 2=Ends, 3=Contains
'FileNameFilterStr : Search string used when FileFilterOption = 1, 2 or 3
You see that you that you have a lot of options with this function and it is easy to add/change extensions.
Note: This function is only working in the Mac Excel versions 2011 or higher
Download the example workbook
File date: 9-Dec-2019