Extract exact value from a string, if value from string is present in predefined list (Excel and/or MySQL)

user_15888637 0
Viewed 65

I have a list of string like:

string
---
112358 lalala lalala  
853211 lala lala 11$  
lalala 1123 lalala 100%

and a specific list of:

ID's
---
112358  
853211   
1123

The aim is to substract from each string the ID value, if within the strings there actually is an ID (from list B) i.e.

string   | matched ID  
ID 112358 lalala lalala  | 112358  
853211 lala lala 11$ | 853211  
lalala 1123 lalala 100%| 1123  
lalala 100% | null

I found one way of doing this in Excel (but not exactly as mentioned above) - by searching if the ID value is contained in the string, like

=if(countif([RANGE OF STRINGS]," * "&[CELL WITH ID]&" * ")>0,"yes","no") which would return "yes" if ID is found in any of the strings

Is there a way of doing it the other way around? To extract the ID value from each string, if there actually is an ID present in any of the string (based on the list B)?

Interested in ways of doing this in either Excel or MySQL

excel-formula excel mysql
2 Answers

Excel:

enter image description here

Formula in B2:

=@FILTER(E$2:E$4,ISNUMBER(FIND(" "&E$2:E$4&" "," "&A2&" ")),"")

MySQL: Highly inspired through this post.

create table t1(str varchar(100));
insert into t1 (str) values
       ("112358 lalala lalala"),
       ("lalala 1123 lalala 100%"),
       ("abc 123 def"),
       ("853211 lala lala 11$");

create table t2(id integer);
insert into t2(id) values
       (112358),
       (853211),
       (1123);

SELECT t1.str
      ,t2.id
FROM   t1
LEFT JOIN t2 ON t1.str LIKE CONCAT('% ', t2.id, ' %')
             OR t1.str LIKE CONCAT(t2.id, ' %')
             OR t1.str LIKE CONCAT('% ', t2.id);

Prints:

str id
112358 lalala lalala 112358
lalala 1123 lalala 100% 1123
abc 123 def NULL
853211 lala lala 11$ 853211
user_97581940

You could use the find formula within a macro. Loop through each string and for each one loop through the list of ID's doing a find against the string.

For the code below I've put the string in Column A and the IDs in Column F, the answer is displayed in Column B

Sub Macro1()

    Dim lRow1, lRow2, lCount1, lCount2 As Long
     
    Range("A1").Select
    Selection.End(xlDown).Select
    lRow1 = ActiveCell.Row
    Range("F1").Select
    Selection.End(xlDown).Select
    lRow2 = ActiveCell.Row
    
    For lCount1 = 1 To lRow1
        For lCount2 = 1 To lRow2
        If InStr(1, Range("A" & lCount1).Value, Range("F" & lCount2).Value, vbTextCompare) = 0 Then
        Else
        Range("B" & lCount1).Value = Range("F" & lCount2).Value
        End If
        Next
    Next
End Sub
user_198035670
Related