UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Wildcards In A Formula, Office 2013    
 
   
halefamily104
post Jan 16 2020, 02:01 PM
Post#1



Posts: 529
Joined: 7-November 10



Hello Again!

Thanks to Debaser from UA, I have this terrific formula that returns the date a code happened:


=IFERROR(LOOKUP(2,1/FIND(CX$13,$AA16:$CQ16),$AB$14:$CR$14),"")

I have a question. CX13 refers to HV. In AA16:CR16, there could be an instance of AHV instead of HV. How can I add wildcards into this formula so it will only work for instances of HV instead of AHV?

Thanks
Go to the top of the page
 
June7
post Jan 16 2020, 03:53 PM
Post#2



Posts: 1,222
Joined: 25-January 16
From: The Great Land


I don't think wildcard will help with Find function. String "HV" is a substring of "AHV" and will match. Wildcard cannot prevent that.

Do this test in VBE immediate window:

?"HV" like "HV"
True

?"AHV" like "HV"
False

?"HV" llike "??"
True

?"AHV" like "??"
False

Unfortunately, I don't know how to translate that into Excel formula.
This post has been edited by June7: Jan 16 2020, 04:45 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Debaser
post Jan 17 2020, 07:18 AM
Post#3



Posts: 179
Joined: 11-October 18



What you actually want is a delimiter. Since you are using commas in the cells, you can append and prepend those to the search values and lookup table too like this:

=IFERROR(LOOKUP(2,1/FIND(","&CX$13&",",","&$AA16:$CQ16&","),$AB$14:$CR$14),"")


Go to the top of the page
 
halefamily104
post Jan 17 2020, 10:25 AM
Post#4



Posts: 529
Joined: 7-November 10



Thanks Debaser!

It works but now its not looking in the cell that has multiple values. It will return the date for the code HV and not AHV (original problem) but its not returning the date for the cell that has AHV, TX, HV in one cell.

Any ideas?
Go to the top of the page
 
Debaser
post Jan 17 2020, 10:31 AM
Post#5



Posts: 179
Joined: 11-October 18



Do you have spaces after the commas that appear in the cells? If so, try this:

=IFERROR(LOOKUP(2,1/FIND(", "&CX$13&", ",", "&$AA16:$CQ16&", "),$AB$14:$CR$14),"")
Go to the top of the page
 
halefamily104
post Jan 17 2020, 11:11 AM
Post#6



Posts: 529
Joined: 7-November 10



Thanks Debaser!

That was the one!

You are awesome!

Have a great rest of your day and notworthy.gif
Go to the top of the page
 
Debaser
post Jan 17 2020, 11:12 AM
Post#7



Posts: 179
Joined: 11-October 18



You're welcome - you too! smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 10:17 AM