Menu
Find/Replace with wildcard, or other solution - using Excel for Mac 2011 When I tried to copy/paste or drag the amended formula, I couldn't prevent excel from changing the range of the lookup array. In this tip you'll find 3 ways to remove carriage returns from Excel cells. You'll also learn how to replace line breaks with other symbols. All solutions work for Excel 2016, 2013 - 2003. There can be different reasons for line breaks occurring in your text.
Anne-Mie realizes that she can use wildcards (*?) to search in Excel, but she wonders if she can use wildcards in the replace string. For instance, she would like to search for 'ab*de' and replace it with 'aa*de', where the asterisk represents any number of characters, or none at all.
The short answer is that there is no way to do this in Excel, as described. If you only wanted to convert the second character of a text value from 'b' to 'a', then that can be done rather easily:
This, however, is probably not what you want to do; you want a way to use wildcards in the 'replace with' text. The technical term for doing such string replacements is called REGEX, which is short for Regular Expressions. REGEX started with languages like Perl but was so powerful that many other programming languages added it on.
The VBA used in Excel is no exception. REGEX was added to Visual Basic 6.0, which means that it made its way to Excel's VBA in Excel 2003. The first step in using REGEX is to turn it on. You do this in the VBA Editor by choosing Tools | References and then making sure there is a check mark next to the Microsoft VBScript Regular Expressions 5.5 option.
![Mac Mac](/uploads/1/2/5/7/125718524/472069358.jpg)
Enabling this reference allows you to create REGEX objects. These objects possess a Test method and a Pattern property. This means that you set the Pattern property, and then the Test method checks to see if the pattern exists. A REGEX object also has a Replace method, which is used to do replacements.
Before proceeding, it is important to understand that regular expressions can get very complex and, well, 'geeky.' There is no way around it; how to work with regular expressions has been the subject of entire books. Fortunately, for the purposes of this tip, the expressions are rather simple in nature. In this case we'll use the pattern '^ab.*de$'. This pattern refers to a word that starts (indicated by the ^) with 'ab' followed by an arbitrary expression (indicated by *) consisting of at least one character (indicated by the period) and ending (indicated by the $) with 'de'.
Here is the code that implements the use of the REGEX object to do the actual replacements.
To use this macro, start with the strings you want to change in column A. Assuming that the first string is in cell A1, you could place the following into another cell in order to get the changed text:
This tells the macro that the pattern you want to look for is '^ab.*de$' (the first parameter), and that you want to replace '^ab' with 'aa'. This formula can be pasted down the column, and you end up with a conversion of column A where the string 'ab*de' is replaced by 'aa*de'.
If you are using an older version of Excel that does not allow you to create REGEX objects, or if you would prefer not to do so, then you can create a macro that will simply step through a group of selected cells and look for any cell that begins with 'ab' and ends with 'de', and then replaces the beginning part with 'aa'.
To use this routine, simply select the cells you want to change, and then execute the macro. You should also make changes to the sFindInitial, sReplaceInitial, sFindFinal, and sReplaceFinal variables, as needed.
Loop through Files in Folder on a Mac (Dir for Mac Excel)
Only for O365 Excel 2016 and above with the latest updates
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.
For Excel 2011 and 2016, see the other option above if you run 2016 and up.
Dir on a Mac is not working the same as in Excel for Windows, you can not use a Filter and wildcards on a Mac like in Excel for Windows like this to get all Excel files
FilesInPath = Dir(folderpath & '*.xl*')
FilesInPath = Dir(folderpath & '*.xl*')
Or this to get only xlsm files
FilesInPath = Dir(folderpath & '*.xlsm')
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 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 and 2016
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 and 2016
Download the example workbook
File date: 4-Dec-2016
File date: 4-Dec-2016