[Solved] Check cell against array content - display if TRUE

[morphingstar]

I have some strings which I want to handle according to some part of the string.
Example of a string table:
stringaaaa.mp4 morechars
stringbbbbb.mp3 morechars123
stringccccccc.txt morechars123xy

I want a table which only displays strings which contains one of .mp3 .mp4 .txt .
Layout
ColC contains the raw strings starting at row 101 (>100 entries), in the example below the row numbers are different.
Array sits on range d91:d93, when created.
ColAC will be used for the resulting string. The original and any assistive columns will be on the left of ColZ.
Goal
Strings which do not include one of the array's contents should not be displayed, or will be filtered out with AUTOFILTER.

So far I developed this formula:
=MID($C36;$I36;SEARCH($H36;$C36)) copied from row 36, ColG
Table
ColC ColD Descr. ColE ColF ColG func.result ColH Col_i
abcdefghijkl.mp3 12345qwertz Len - Search abcdefghijkl. .mp3 1
abcdefghijkl.mp4 12345qwertz Len - Search abcdefghijkl. .mp4 1
abcdefghijkl.txt 12345qwertz Len - Search abcdefghijkl. .txt 1
string ColC control cells for develop. result reference Start point of MID
=MID($C36;$I36;SEARCH($H36;$C36)) copied from row 36
As you see, I need a reference for every row: .mp_ or .txt .
If the reference was inside an array (range) and the check would use _OR_ _-LOOKUP_ _IF_ then the 2 reference columns could be eliminated. An array could be easily modified, no need to run through every entry row.

Comments:
The MID start point reference is optional. In this application it would be(include) the first character of the string. I included SP for completion and portability.
abcdefghijkl. ignore the dot at the end of the result. Ii is not important, just an uncorrected accuracy error in the MID-string definition.

I checked program HELP for _OR_ _-LOOKUP_ _IF_ , but failed to build what I need.

The result might be helpful to some people who design tables for their files.
The web page eliminates spaces, the columns are not displayed as I wrote them.

aligntable: eliminated
Table= eliminated

Code:

Code: Select all

So far I developed this formula:
 [b]=MID($C36;$I36;SEARCH($H36;$C36)) [/b]     copied from row 36, ColG
[b]Table[/b]
ColC                                          ColD Descr.            ColE   ColF     ColG func.result  ColH          Col_i
abcdefghijkl.mp3 12345qwertz	Len - Search			         abcdefghijkl.	  .mp3	    1
abcdefghijkl.mp4 12345qwertz	Len - Search			         abcdefghijkl.	  .mp4	    1
abcdefghijkl.txt 12345qwertz	Len - Search			         abcdefghijkl.	  .txt	    1
string ColC                                control cells for develop.             result                 reference  Start point of MID
                                                                                                 =MID($C36;$I36;SEARCH($H36;$C36))      copied from row 36

SEE ATTACHED PICTURE, as CODE is messing up too, though less than _table_.
Thanks for solutions.

[robleyd]

I want a table which only displays strings which contains one of .mp3 .mp4 .txt .

As SEARCH is capable of using regular expressions, can you not use a regex as your search term? Something like

Code: Select all

SEARCH("[mp.][txt]"; $C36)

Note; my regex-fu is weak, so that may not be syntactically correct; I'm sure someone will enlighten us

[morphingstar]

I found a solution which serves - even simpler than originally thought.
See attached screen copy which shows results and formulas.
For me the problem is solved, without the need for an array.
The result is sortable
Tks for looking.

Click the picture to view full size. Copy returns useless version, I don't know why.
Here is a text copy of the various cells, if it helps.
Col C, Row 52 apply top left

File_List fixed pos $: . 3 ex: .wav =4 File Name file type added info
abcdefghijklx.mp3 asdfgqwer Len & Find + MID 27 14 13 abcdefghijklx mp3 asdfgqwer
abcdefghijklx.mp4 asdfgqwertz 15 abcdefghijklx mp4 asdfgqwertz
abcdefghijkl.txt yxcvbnm abcdefghijkl txt yxcvbnm abcdefghijkl.txt yxcvbnm
fixed pos. $ name / type split dotfree type control above combine
LEN($C53) FIND($E$52;C53) LEFT(C55;FIND($E$52;C55)-1)
LEN($C54)-FIND($E$52;C54)
MID(C55;FIND($E$52;C55)+1;$F$52)
RIGHT($C55;LEN($C55)-FIND($E$52;C55)-$F$52)
CONCATENATE($H55;$E$52;$I55;$J55)
Col stat C, Row start 52