2

I am searching for cells that have the word aldi in the filter dropdown. So I choose "Contains" and enter aldi.

This is bringing back cells that contain Geraldine etc.

I've had some success using aldi * with the space and asterisk, which removes results like Geraldine. But still contains results like Vivaldi.

I don't mind if the string contains another word along with aldi, e.g. Aldi shop 123. I just want to make sure the instance of aldi is is by itself.

Sample of data:

GERALDINE
RISTORANTE GARIBALDI
ALDI
PORTA GARIBALDI
GRIMALDI
ALDI STORES

Screenshot is what I have done so far.

  • 1
    Is Aldi always the first word in the desired cells? – Michael Harvey Jan 17 at 10:17
  • Not always. It could say "New York Aldi". There are at least 100 different store names that I need to standardize. For example once I clean up Aldi, I will try to standardize all KFC. So I will have to search for all instances of KFC stores e.g. "New York KFC" or "1235 KFC Toronto" or "KFC NY" ... but omit "UKFC Football Club" for example. I need KFC / Aldi etc. to be on their own in the string. – SCool Jan 17 at 12:19
  • What if it was always the first word? Use "beings with" ? – SCool Jan 17 at 12:23
  • should be * aldi *, asterik before and after (with spaces too). But it would be a problem if the cells ends with ` aldi` with no space... – gns100 Jan 17 at 22:08
2

First enter the following User Defined Function in a standard module:

Public Function aldi(sIn As String) As String
    arr = Split(LCase(Replace(sIn, ".", "")))
    aldi = "h"
    For Each a In arr
        If a = "aldi" Then
            aldi = "s"
        End If
    Next a
End Function

It will return either "s" for "show" or "h" for "hide"

If your data is in column A, then in B2 enter:

=aldi(A2)

and copy downward:

enter image description here

Then filter column B to display only the "s" rows:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=aldi(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#1:

The code line:

arr = Split(LCase(Replace(sIn, ".", "")))
  • takes the phrase or sentence and converts it to lower case; so Aldi and aldi are treated the same.
  • it removes periods so any aldi. are considered "show"
  • it creates an array of words using the space character as the separator.
  • it seaches for "aldi" in that array
| improve this answer | |
  • Thanks, I'll try that. What exactly are you doing here? arr = Split(LCase(Replace(sIn, ".", ""))) – SCool Jan 17 at 14:06
  • @SCool See my EDIT#1 – Gary's Student Jan 17 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.