need formula or vba to find exact string through list and find exact correct string

Hi,

I can't see any reasonably simple string slicing formulae that can do this.

Can I suggest you try downloading the fuzzy lookup Excel add in from Microsoft which you'll find here. https://www.microsoft.com/en-gb/down....aspx?id=15011

I've used it on a few occasions and it generally comes up with the goods.

c5=vlookup(right(A5,len(A5)-search(" ",A5,1))&"*",$I$1:$I$25,1,0)

Originally Posted by oeldere

c5=vlookup(right(A5,len(A5)-search(" ",A5,1))&"*",$I$1:$I$25,1,0)

That works for a few but the structure of the strings is not consistent.

Please Login or Register  to view this content.

yes I know but i think it is a good way to start.

Originally Posted by johnlara

Ya sir its little complicated through formulas any possible way of VBA code can accomplish my task? any experts

Hi,

How long is the list you're trying to correct. If it's relatively small and doesn't change then I'd take the pragmatic approach and manually create the results. However I suspect the list is much larger and this was just a small sample.

The problem is the inconsistency and the more records there are the more the number of variations and the more conditions that any macro needs to know about.
If you could pre-define a finite list of variations then that's one thing but I suspect that's not the case.

Have you tried the fuzzy lookup I mentioned in post #2. You can set the confidence level of any lookup and you'll get the correct results for a significant number of records leaving you to manually correct a smaller subset.

However whatever method you use, whether a macro (assuming it's possible to code one) or a fuzzy lookup you will need to use the Mk.1 eyeball to check for False positives, i.e. strings which the method reports as being a valid correction but which on examination isn't.

Try the Fuzzy lookup first.