Using Instr function with unknown starting position to search for substring in a cell

[excel5028000]

Hi MrExcel and Everyone,~ I hope you're all doing great, fine and well at this time!

I need help with the Instr function, or please let me know if a better function exists in Excel/VBA, that would work almost like fuzzy logic to look at data in a cell, and determine if a specific string is present. The Instr function requires a starting position of character in order to work; even though it's supposed to be optional.

Instr( [start], string, substring, [compare] )

However my dilemma is what if you don't know the starting position. What if the position can be any where? This is best illustrated by sample data below. On column A, I have some dirty data that gets spitted out from a bank let's say. (Image below is after running macro with output on column B).

Over time, I learned to create a cross reference table visually searching the bank data for a specific string, and if that string exists then I would pull the clean data or in this example the Legal Name of Entity, its equivalent.
Here's a simple two loop VBA code I am trying. But if you notice, it only works if the [start] position of the dirty data is one.

Sub Test()

For II = 2 To 12
For JJ = 2 To 5

If InStr(1, Cells(II, 1), Cells(JJ, 7), vbBinaryCompare) = 1 Then


    Let Cells(II, 2) = Cells(JJ, 8)
    GoTo 550
    End If
    

Next JJ
550
Next II
End Sub

Do I do vbBinaryCompare? or vbTextCompare? I tried both.

I tried =>   If InStr(1, Cells(II, 1), "*" & Cells(JJ, 7) & "*", vbTextCompare) = 1 Then

Come to think of it now, I suppose we could do a process using LEN(A2) find length of each cell on column A, then create a loop changing the starting position each time ==> from 1 to value of the LEN output. But I am not there yet. I am hoping someone knows; please even employ xlookup or any function that will do the job.

Here's actual text data for copy pasting.

DIRTY_BANK_DATA_DOWNLOAD						Lookup_String	LEGAL_NAME_ENTITY
0121D ,8760655567,Champion Energy ,Primary Groups,						Champion	Champion Energy Commerical Company
ACHOUT ,9062765001,VOXX PAY,Primary Groups,						VOX	Voxx International Company
EDI PAYMNT,6113666506,DYNEGY,Primary Groups,						DYNEGY	Dynegy Energy Company
EDI PAYMNT,1813015683,NEW YORK G&P LLC,Primary Groups,						TXU	TXU Energy
McCoy Electric BILL 0201902700
TXU ENERGY TXU ACH 10015265514	TXU Energy
Champion Energy 0121D	Champion Energy Commerical Company
Sprint Payment 033020 757113004BDE9S
OXY Refinery PAYMENT 200401 0000786255
ACHOUT ,9062762145,VOXX PAY,Primary Groups,
TXU ENERGY TXU ACH 106942628290	TXU Energy

Thank you very much!!!

 

[Trebor76]

Hi excel 5028000,

See if this formula is what you're after which can be put into a macro if needed:

=IFERROR(VLOOKUP(LEFT(A1,(SEARCH(" ",A1,1)-1))&"*",G:H,2,FALSE),"")

It assumes the raw data is in Col. A and the lookup table is spread across columns G and H (change to suit).

Regards,

Robert

 

[steve the fish]

INSTR finds the position of the search string in the string and produces a number. If the search string is not found INSTR will produce a zero. Armed with that you should make your IF test

If InStr(1, Cells(II, 1), Cells(JJ, 7), vbTextCompare) >0 Then

 

[Peter_SSs]

I am also wondering if you really need to be using a macro? Would this formula be sufficient?

excel5028000 2020-04-04 1.xlsm
	A	B	C	D	E	F	G	H
1	DIRTY_BANK_DATA_DOWNLOAD						Lookup_String	LEGAL_NAME_ENTITY
2	0121D ,8760655567,Champion Energy ,Primary Groups,	Champion Energy Commerical Company					Champion	Champion Energy Commerical Company
3	ACHOUT ,9062765001,VOXX PAY,Primary Groups,	Voxx International Company					VOX	Voxx International Company
4	EDI PAYMNT,6113666506,DYNEGY,Primary Groups,	Dynegy Energy Company					DYNEGY	Dynegy Energy Company
5	EDI PAYMNT,1813015683,NEW YORK G&P LLC,Primary Groups,						TXU	TXU Energy
6	McCoy Electric BILL 0201902700
7	TXU ENERGY TXU ACH 10015265514	TXU Energy
8	Champion Energy 0121D	Champion Energy Commerical Company
9	Sprint Payment 033020 757113004BDE9S
10	OXY Refinery PAYMENT 200401 0000786255
11	ACHOUT ,9062762145,VOXX PAY,Primary Groups,	Voxx International Company
12	TXU ENERGY TXU ACH 106942628290	TXU Energy
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH($G$2:$G$5,A2),$H$2:$H$5),"")

 

[Peter_SSs]

If you do want to use vba, I would employ a user-defined function (without Instr)

Function LegalName(sDirty As String, rTable As Range) As String
  Dim d As Object, RX As Object
  Dim a As Variant
  Dim i As Long
  
  a = rTable.Value
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Join(Application.Transpose(Application.Index(a, 0, 1)), "|") & ")"
  If RX.Test(sDirty) Then LegalName = d(CStr(RX.Execute(sDirty)(0)))
End Function

excel5028000 2020-04-04 1.xlsm
	A	B	C	D	E	F	G	H
1	DIRTY_BANK_DATA_DOWNLOAD						Lookup_String	LEGAL_NAME_ENTITY
2	0121D ,8760655567,Champion Energy ,Primary Groups,	Champion Energy Commerical Company					Champion	Champion Energy Commerical Company
3	ACHOUT ,9062765001,VOXX PAY,Primary Groups,	Voxx International Company					VOX	Voxx International Company
4	EDI PAYMNT,6113666506,DYNEGY,Primary Groups,	Dynegy Energy Company					DYNEGY	Dynegy Energy Company
5	EDI PAYMNT,1813015683,NEW YORK G&P LLC,Primary Groups,						TXU	TXU Energy
6	McCoy Electric BILL 0201902700
7	TXU ENERGY TXU ACH 10015265514	TXU Energy
8	Champion Energy 0121D	Champion Energy Commerical Company
9	Sprint Payment 033020 757113004BDE9S
10	OXY Refinery PAYMENT 200401 0000786255
11	ACHOUT ,9062762145,VOXX PAY,Primary Groups,	Voxx International Company
12	TXU ENERGY TXU ACH 106942628290	TXU Energy
Sheet3

Cell Formulas
Range	Formula
B2:B12	B2	=LegalName(A2,G$2:H$5)

 

[excel5028000]

Good day everyone--Thank you each for helping.

Robert: I greatly appreciate the attempt and lesson - however the VLOOKUP only works if Lookup_String starts at left (or at starting position) of the Dirty_Bank_Data on column A; but not if in random middle position. Problem lies in bank data being an amalgamation of different entities (read peoples) and as such don't use a uniform approach at time of data entry in the Memo field when making transaction.

steve the fish – novel approach! It's as if you pivoted in the thinking process, whereas I searched if output equaled 1, you did if output is greater than 1. Macro indeed works with InStr.

Peter_SSs: Agree, absolutely don't need use of macro. The formula suffices, could not have fathomed it. Brilliant! In fact, most appropriate for sharing with fellow office workers compared to a macro given we're of the accountants sort and not tech coders type, not patronizing anyone here but preference is on something fast and "easy". And I'll be sure to attribute credit to this forum.

If I may, if you don't mind just for sake of knowledge, how would you modify your user-defined function to pick up a string within a larger continuous string. To clarify, in instance below for data in column A, it works if Lookup_String = "McCoy" but doesn't if Lookup_String = "Coy".

McCoy Electric BILL 0201902700

Thank you, Gentlemen, Best wishes!

 

[Peter_SSs]

Thank you, Gentlemen, Best wishes!

You're welcome. .. and to you.

how would you modify your user-defined function to pick up a string within a larger continuous string.

RX.Pattern = "\b(" & Join(Application.Transpose(Application.Index(a, 0, 1)), "|") & ")"
RX.Pattern = Join(Application.Transpose(Application.Index(a, 0, 1)), "|")