VBA to search a text string to check if a value from a list is part of the text string

kmht0307

New Member
Joined
Aug 20, 2010
Messages
8
Good morning,

I have a downloaded file of bank transactions and in the description column, that contains a string of text and numbers, I want to be able to establish whether a customer reference, which could be numbers or text, is part of the description string.

So for instance:

The description on one line is:

FRANCOTYP POSTALIA 503747 058027 DDR

In my list of customers, the reference number for a customer is 058027 which matches the above string, so I want to return the value 'AR' in the cell to the right of the description column, which is column 'N'

<tbody>
</tbody>

If it is then I want to be able to categorise that transaction as being 'AR'.

I have tried to use INSTR but that only seems to work if you have a specific search criteria.

Any help would be appreciated.:rolleyes:
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Hello. No need to use VBA here; you can do this with a normal worksheet function.

Suppose your description is in cell N2, and your list of customer references are in A1:A20.

Then you can enter this as an array formula (i.e. via Ctrl+Shift+Enter) into cell O2, and drag it down:

Code:
= IF( SUM( COUNTIF( N2, "*" & IF( $A$1:$A$20 = "", CHAR( 191 ), $A$1:$A$20 ) & "*" ) ) > 0, "AR", "" )
IMPORTANT: You must enter this using Ctrl+Shift+Enter otherwise it won't work!
 

kmht0307

New Member
Joined
Aug 20, 2010
Messages
8
Many thanks for this, the reason why I wanted to explore a VBA option is that the file is rather large and the user wanted to have a non formula solution to this. And also this step is part of a larger process and so it needs to be automated.

Is there any way to use VBA to enter this array formula into each cell in the range N2 to say N100 (would be a growing or shrinking range)?
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
Office Version
2016
Platform
Windows
I want to be able to establish whether a customer reference, which could be numbers or text, is part of the description string.
Is there a predetermined number of substrings in the string if the customer reference is present? If absent?
Does the placement of the customer reference change? If so, how do you differentiate it?

The following code assumes the string is delimited by a space, string has at least 5 elements and if there are 5 or more then the customer reference is the 4th, and that input is in column M and output in Column N. If there are less than 5 elements then Nothing will happen.

Set First_Row_WData= to the first row with data you want the code to run on.
If you want the code to run till the bottom of the used range on the worksheet, then do nothing. Otherwise adjust Last_Row_WData.

Code:
Sub For_User()

Dim STR() As String, L As Long, WS As Worksheet, First_Row_WData As Long, Last_Row_WData As Long

With Application
                .ScreenUpdating = False
                 .Calculation=xlCalculationManual
End With


Set WS = ThisWorkbook.Worksheets("Sheet Name goes here") 'You can replace this with a sheet codename


ROW_Count = WS.UsedRange.Rows.Count


First_Row_WData=2

Last_Row_WData= ROW_Count

For X = First_Row_WData To Last_Row_WData


    STR = Split(WS.Range("M" & X).Value2, " ") 'string is now delimited with a space
    
    If UBound(STR) >= 4 Then
    
        WS.Range("N" & X).Value2 = "AR"
        
    End If
    
Next X


With Application
                .ScreenUpdating = True
                 .Calculation=xlCalculationAutomatic
End With


End Sub
 
Last edited:

kmht0307

New Member
Joined
Aug 20, 2010
Messages
8
Thanks MoshiM, usually the string would be as follows:

Customer Name (could be spaces between), Customer Number, Another Reference(sometimes) and the text 'DDR'

So for 2 examples of the actual data I am looking at:

MANITOU FINANCE LI X0008784 U1014777 DDR
DIRECT RESPONSE LT BS112030 DDR

So I want to effectively look up the values 'X0008784' and 'BS112030' from a customer list and if they are present in the list return the text 'AR' and if not return the text 'DDR' in the column adjacent to the string.

So the placement of the customer number could be different in each string.

<tbody>
</tbody>



<tbody>
</tbody>
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
Office Version
2016
Platform
Windows
You will need to create a column that will hold Customer references that correlate to the string in the same row and column M.
An alternative to this would be to place a unique character somewhere in the string if it holds a customer reference and make some minor adjustments to the instr function below. If the unique character is unwanted in the end, then you can use the replace function to remove it.

You could set up an array to hold customer references as well and just search that.

Code:
Sub For_User()

'Dim C_Ref() as string
Dim STR As String,  WS As Worksheet, First_Row_WData As Long,Last_Row_WData As Long,Item as variant


With Application
                .ScreenUpdating = False
                .Calculation=xlCalculationManual
End With


Set WS = ThisWorkbook.Worksheets("Sheet Name goes here") 'You can replace this with a sheet codename


STR="Letter of column that will hold customer references in the same row that column M is searched"

'C_Ref= WS.range("Range of customer references goes here").value2

ROW_Count = WS.UsedRange.Rows.Count


First_Row_WData=2


Last_Row_WData= ROW_Count


For X = First_Row_WData To Last_Row_WData
  
    If instr(1,WS.RANGE("M" & X).value2,WS.RANGE(STR & X).value2)>0 then
    
        WS.Range("N" & X).Value2 = "AR"
        
    End If
'For each Item in C_Ref
        
'If instr(1,WS.RANGE("M" & X).value2,C_Ref)>0 then
    
        'WS.Range("N" & X).Value2 = "AR"
        
    'End If
'Next Item    
'Next X


With Application
                .ScreenUpdating = True
                .Calculation=xlCalculationAutomatic
End With


End Sub
 
Last edited:

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
Office Version
2016
Platform
Windows
Code:
[COLOR=#333333]'If instr(1,WS.RANGE("M" & X).value2,C_Ref)>0 then[/COLOR]
Should be

Code:
[COLOR=#333333]'If instr(1,WS.RANGE("M" & X).value2,Item)>0 then[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,101,953
Messages
5,483,865
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top