Macro to substitute consonants for ? for Boolean search

**hadydea** · 03-04-2020, 04:22 PM

I am honestly not sure if this is a macro or a formula question.

My company uses boolean strings a lot to do database searches. But it can be a bit time consuming to constantly type them out. I am hoping that a macro or a formula might help speed the process up.

If in column A we have the word "formula", in column B I need this to appear "formula OR ?ormula OR fo?mula OR for?ula OR formu?a" so that it can be copied and pasted in our database search engine.

Our database already has a vowel substitution option so I only need to figure out how to get the consonants to be substituted.

Any ideas?

I haven't posted a workbook example because we don't have anything like this. I just want to create a new workbook where we can copy and paste our search terms and get the boolean string.

Any ideas??

**jeffreybrown** · 03-04-2020, 04:39 PM

I think you will need to post a sample workbook as it appear the next question is: How do I do the same think with a different word? A representative look at your sample will help craft the right response.

Please see the "Yellow" banner at the top.

With what you posted...

=A1& " OR " & SUBSTITUTE(A1,"f","?")& " OR " & SUBSTITUTE(A1,"r","?")& " OR " & SUBSTITUTE(A1,"m","?")& " OR " & SUBSTITUTE(A1,"l","?")

**hadydea** · 03-04-2020, 04:54 PM

I'm honestly not sure a workbook would help, but here's one.
In the first column, I have the search terms that we need to run through our database. But we have to have a boolean and break apart the word. Currently, we have to type it all out and manually substitute each consonant. We can't do it all at once because that then creates too many options.

**Rick Rothstein** · 03-04-2020, 05:31 PM

For the layout shown in your example file, does this macro do what you want (note I outputted to Column D, but you can change that to any other location by modifying the left side of the last line of code)...

Please Login or Register  to view this content.

**FlameRetired** · 03-04-2020, 05:48 PM

What is the layout you want the output in? Concatenated all in one cell, separated across columns, separated across rows?

Concatenated. In D5 and filled down

Formula:

Please Login or Register  to view this content.

Edit or since you have Office 365 this is easier

Formula:

Please Login or Register  to view this content.

**hadydea** · 03-04-2020, 06:16 PM

Thanks Dave.

The output I am hoping to get is what is in the second column. The long text string with all the ?’s. The goal is to have our search term, in this example formula, repeated, and in each new repetition, have one of the consonants replaced with a ? And separated by “OR”

**hadydea** · 03-04-2020, 06:17 PM

Thanks Rick,

I’ll give this a try and let you know!!

**jeffreybrown** · 03-04-2020, 06:18 PM

Very nice Rick and thanks for the assist.

Dave, thank you also, but since I do not have 365, I will not be able to follow you here.

**jeffreybrown** · 03-04-2020, 06:22 PM

Hi hadydea,

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

**FlameRetired** · 03-04-2020, 06:25 PM

Originally Posted by hadydea

Thanks Dave.

The output I am hoping to get is what is in the second column. The long text string with all the ?’s. The goal is to have our search term, in this example formula, repeated, and in each new repetition, have one of the consonants replaced with a ? And separated by “OR”

In that case this small adjustment.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 03-04-2020, 06:36 PM

Originally Posted by jeffreybrown

...
Dave, thank you also, but since I do not have 365, I will not be able to follow you here.

Ooops. I started to edit your post with this

Is these cases I use tigeravatar's CONCATALL Udf.

Please Login or Register  to view this content.

Then the formula array entered: Edited for the wildcard "?".

Formula:

Please Login or Register  to view this content.

**hadydea** · 03-06-2020, 09:09 AM

I can't see if my reply went through, but this worked!!!!
THANK YOU SO SO SO MUCH!

YOU ARE A MAGICAL WIZARD!!

**jeffreybrown** · 03-06-2020, 09:58 AM

You are very welcome. We are happy to help and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Macro to substitute consonants for ? for Boolean search

LinkBack

Thread Tools

Rate This Thread

Display

Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Re: Macro to substitute consonants for ? for Boolean search

Thread Information

Users Browsing this Thread

Similar Threads

Boolean search - AND, NOT, and OR

[SOLVED] Are first 2 letter consonants ?

Macro to delete consonants

[SOLVED] Exit Workbook_BeforeClose(Cancel As Boolean) if a public boolean = true

Help: Trying to boolean search down a column and note matches.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) problem

Name Search in Excel User Sheet / Boolean?

Bookmarks

Bookmarks

Posting Permissions