Customn column excel code to truncate multiple extension name

[anmac1789]

hello, I've run into a problem with using excel functions with everything. I had a ransomware infection one time and it added all my .jpg snapchat pictures with extension .pcqq to look like filename.jpg.pcqq. Luckily, I had some original pictures saved elsewhere to pair up infected files with original files. Now, I am using custom columns to pair up the jpg.pcqq and .jpg files but the filenames truncate the last part of the filename. Here is an example,

Screenshot 2024-01-27 005023.jpg (44.61 KiB) Viewed 917 times

Code: Select all

 column1:=stem: column2:=IF(ISERROR(SEARCH(".???",column1:)),LEFT(column1:,LEN(column1:)),IF(SEARCH(".???",column1:)<>ISERROR(SEARCH(".???",column1:)),"yes",LEFT(column1:,LEN(column1:)-4)))

if I substitute column1: for any cell in excel, then the code works:

Screenshot 2024-01-27 005023.jpg (44.61 KiB) Viewed 917 times

I want to apply the long column2 code on column1 so that all the filenames have the same 'base name'. What should I do ?

[NotNull]

Not sure if I understand, but try this and let us know which parts fit youyr needs and which parts don't:

Code: Select all

ext:jpg;pcqq   regex:"^(.*)(\.jpg$|\.jpg\.pcqq$)"   dupe:regmatch1

(add the Regular Expression Match 1 column if you would like to see the "basename" )

[anmac1789]

Not sure if I understand, but try this and let us know which parts fit youyr needs and which parts don't:

Code: Select all

ext:jpg;pcqq   regex:"^(.*)(\.jpg$|\.jpg\.pcqq$)"   dupe:regmatch1

(add the Regular Expression Match 1 column if you would like to see the "basename" )

The problem seemed to have been ISERROR() it didn't work with everything but, ISBLANK() works instead. Although the solution with ISBLANK() seems rather long but it does have nested IF conditions (as seen in the excel screenshot). I am wondering if the code either with regex or with excel functions could be simplified.

Well anyway, I tried your code it only shows the full path of each filename without extensions.

Code: Select all

dupe:regmatch1

This did not show any result for some reason However, when I removed this part then the regex column-1 showed results.

Code: Select all

ext:jpg;pcqq regex:"^(.*)(\.jpg$|\.jpg\.pcqq$)"

What if I had different type of extensions ? for example there is .mp4.pcqq and .jpg.pcqq and png.pcqq. Instead, I used SEARCH(".???",column1:) to get the position of different types of extensions

Is there some command to remove all extensions from filenames that way it would simplify the code ?

[void]

SEARCH() and ISERROR() in Everything are currently quirky.

I'm working on making SEARCH() behave exactly the same as Excel.



SEARCH() in Everything returns a null value when the term is not found.
SEARCH() in Excel will return #VALUE! when the term is not found.

Everything doesn't have a #VALUE! type.



I have added ISERROR() in 1367a
This function is also currently quirky.
For now, I have made ISERROR(x) return 1 when the type of x is null. Otherwise, it returns 0.
For now, you should see the expected results with ISERROR(SEARCH("term","text"))
However, ...


SEARCH() in Everything currently does not support wildcards.
I'll add support in a future update.



For now, please try the stem() formula function.

[anmac1789]

Thank you void, I can't wait for more excel functions to work with everything, this would be such a powerhouse !

[tuska]

... I can't wait for more excel functions to work with everything, ...

Everything 1.5.0.1367a - 8.2.2024 - Added the following Excel functions: ...
... but you probably already know this information.

[anmac1789]

yes i always use the latest version as soon as it comes out