Formula to find a reference number in a cell

[Mouseinalabyrinth]

Hi all. I am trying to find a way to search for multiple instances of a wildcard match in one cell. I want to exclude a specific match from the results. I can't find a way to find and display a list AND exclude certain terms.

I have a sheet which is an export of live chat transcripts. The entire transcript is in one cell. It's a text conversation with some reference numbers thrown in.

In that transcript I want to find a ten-digit reference number wildcard 'inc???????' and display all matches except a specific match 'inc1234567' in a separate cell. I've tried to see if I can start the search after this number, exclude this number, ignore it, but can't find a way to nest how to search the entire cell excluding the specific string containing this number.

Every transcript has 'inc1234567' in it, i just want to ignore it.

I also want to have a separate cell return true/false if there is a wildcard match in the transcript excluding that one specific result. I've tried =IF(ISNUMBER(SEARCH("INC???????", [@TRANSCRIPT])), TRUE, FALSE) but since all of them contain inc1234567 they all come back true.

Hours of googling has beaten me. Any ideas much appreciated.

 

[JamesCanale]

This got ugly in a hurry. Using LET will clean it up.

MrExcelPlayground14.xlsx
	A	B	C
1	Transcript	reference numbers	matches?
2	I am a silly goose inc1234567 and you are a goose inc3323434 and he is not a silly one inc9876543.	inc3323434, inc9876543	TRUE
3	I am a silly goose inc1234567 and you		FALSE
4	I am a silly goose inc1234567 and you are a goose inc2333333 and he is not a silly one inc2345678 and inc3456778.	inc2333333, inc2345678, inc3456778	TRUE
Sheet5

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(TEXTJOIN(", ",TRUE,FILTER(IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),""),IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),"")<>"")),"")
C2:C4	C2	=NOT(ISERROR(ROWS(FILTER(IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),""),IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),"")<>""))))

 

[Peter_SSs]

This got ugly in a hurry.

I agree, and it could get worse. I don't want to put your considerable effort down but the formula could need considerably more given the example below.

Mouseinalabyrinth.xlsm
	A	B
1	Transcript	reference numbers
2	I am a silly goose inc1234567 and have an incapacity to speak with sincereness	incapacity, incereness
Ref Nums (2)

Cell Formulas
Range	Formula
B2	B2	=IFERROR(TEXTJOIN(", ",TRUE,FILTER(IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),""),IFERROR(IF(MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)="inc1234567","",MID(A2,UNIQUE(IFERROR(SEARCH("inc???????",A2,SEQUENCE(LEN(A2))),"")),10)),"")<>"")),"")

@Mouseinalabyrinth

.. AND exclude certain terms.

This sounds like there could be more than one exclusion

see if I can start the search after this number

This sounds like the exclusion will be the fist item that matches the pattern, but it also implies only a single exclusion which seems to contradict the point above. Can you clarify - if the suggestion below does not solve your issue?

Also, would it be possible in the text to have "inc" followed by less than 7 digits or more than 7 digits?

I think that any worksheet formula solution will be very unwieldy, particularly if punctuation can be involved as sounds likely.

Would a user-defined function like below be acceptable? If so, to implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RefNums(s As String) As String
  Dim RX As Object, M As Object
 
  Const sExclude As String = ",inc1234567,inc7777777,"  '<- Add more exclusions if required
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\binc\d{7}(?=\D|$)"
  For Each M In RX.Execute(s)
    If InStr(1, sExclude, "," & M & ",") = 0 Then RefNums = RefNums & ", " & M
  Next M
  RefNums = Mid(RefNums, 3)
End Function

Mouseinalabyrinth.xlsm
	A	B
1	Transcript	reference numbers
2	I am a silly goose inc1234567 and have an incapacity to speak with sincereness about inc7777777
3	I am a silly goose inc1234567 and you are a goose inc3323434 and he is not a silly one inc9876543.	inc3323434, inc9876543
4	I am a silly goose inc1234567 and inc987656432 and you
5	I am a silly goose inc1234567 and you are a goose inc2333333, or inc2345678 and inc3456778.	inc2333333, inc2345678, inc3456778
Ref Nums

Cell Formulas
Range	Formula
B2:B5	B2	=RefNums(A2)

 

[Peter_SSs]

I also want to have a separate cell return true/false if there is a wildcard match in the transcript excluding that one specific result.

For that, couldn't you just reference the reference numbers result as shown in col C below? If you want it to be exclusive of that result then, still using the udf, you could use the col D formula.

Mouseinalabyrinth.xlsm
	B	C	D
1	reference numbers	matches?	matches?
2		FALSE	FALSE
3	inc3323434, inc9876543	TRUE	TRUE
4		FALSE	FALSE
5	inc2333333, inc2345678, inc3456778	TRUE	TRUE
Ref Nums

Cell Formulas
Range	Formula
B2:B5	B2	=RefNums(A2)
C2:C5	C2	=B2<>""
D2:D5	D2	=RefNums(A2)<>""