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
Thanks for solutions.