0

Is there a workaround to compare a text to a pattern in VBA, where the text may contain zero, one or more whitespaces between any two characters?

The text is imported from a bad quality scanned pdf through Acrobat API, for example the original "apple" word in the pdf may end up as "apple" or "a p p l e" or "app l e" or "a pp le" or any other in my text.

check1 = "apple123" Like "apple###"'true
check2 = "apple123" Like "a*p*p*l*e*###" 'true
check3 = "apple123" Like "a p p l e ###" 'false, this is not working
check3 = "a p p l e 123" Like "a*p*p*l*e*###" 'true
check4 = "appricotle123" Like "a*p*p*l*e*###"  'sadly also true, this will find bunch of others

I was hoping for [, ] to use instead of * so searching for nothing or space from a list is working, but the nothing is omitted.

Of course my patterns are more complicated eg. "*decree?###/20##", but solving the main issue is how to get over random spaces.

Zsombor Varkonyi
  • 1
  • 1
    what about `replace("a p p l e 123"," ","") Like "apple###"` – Scott Craner Nov 30 '20 at 21:17
  • 1
    [You could use regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) perhaps. – BigBen Nov 30 '20 at 21:18
  • I think you are on a loser here if there are truly spaces inserted. How would a regex differentiate between 'this apple is' or 'this app lets you'. In this case an (tedious) option is to have a macro that removes spaces from the highlighted text which is triggered by a keypress. You could also investigate text character by character to see is the spaces are caused by kerning or some other character property. If so you may be able to reset back to a default fairly quickly. If you do end up working with character properties start from the end and work to the front. – freeflow Nov 30 '20 at 21:32

0 Answers0