New column from Excel formula

[sk2107]

Hello,
I have a certain case that I need to add/ remove some parts of the full path, I managed to put it in an excel formula, but I could not display it as new column

=MID(A1, FIND("\", A1, 5) + 1, 1) & ":\" & MID(A1, FIND("\", A1, FIND("\", A1, 5) + 1) + 1, LEN(A1) - FIND("\", A1, FIND("\", A1, 5) + 1) - 8)

any suggestion is highly apprecaited

Regards

[NotNull]

A couple of examples of inputstring and desired outputstring would help here ..

[dedupeit]

Do you have an example of a path and what you want the column to display?

[sk2107]

the value in A1
X:\thumbs_27-12-2024_Tosh\J\#PicasaSK\U-x_uniq\J\F#0321-IMG_20190208_084842.jpg.thi.jpg
Result
J:\#PicasaSK\U-x_uniq\J\F#0321-IMG_20190208_084842.jpg

the formula does the following:

(1) removes the text from the beginning until the second "\"
(2) added a ":" after the first character in the result of (1)
(3) removes the last 8 characters from the final string

Regards

[dedupeit]

I'm sure there are better ways to do this but this works

Code: Select all

add-column:a a:=stuff(regexextract(fullpath:,".*?\\.*?\\(.*).{8}"),2,0,":")

[sk2107]

Perfect. Thank you

Best regards

[sk2107]

one more question, how can filter the added column?

Best regards

[dedupeit]

one more question, how can filter the added column?

Best regards

Code: Select all

columna:<your filter term>

[sk2107]

thanks a lot,

Best Regards

[NotNull]

FWIW, your excel formula converted to Everything:

Code: Select all

addcol:A  A:=MID($full-path:,FIND("\",$full-path:,5)+1,1)":\"MID($full-path:,FIND("\",$full-path:,FIND("\",$full-path:,5)+1)+1,LEN($full-path:)-FIND("\",$full-path:,FIND("\",$full-path:,5)+1)-8)

Something to keep in mind: Formulas don't support spaces currently.
Works: eval(1+1). Doesn't: eval(1 + 1). Does work: eval("1 + 1").


I would go with a regex approach too, but a regex-free alternative:
- Use the element() formula and use "\" as the element separator
- Get the third element ("j") of the path (not full filename; explained later) (1)
- Get the position of the 3rd "\" of the path (not full filename; explained later)
- Get the path from that position until the end (2)
- From the filename, get the stem (=name without extension).
- From the remaining, get the stem (3)
- Output = (1) + ":" + (2) + "\" + (3)

The Everything formula turned out to be a bit more complex than I anticipated, but still somewhat readable:

Code: Select all

addcol:A  A:=element($path:,"\",3):substr($path:,find("|",substitute($path:,"\","|",3)))\stem($stem:)

I'm sure there are better ways to do this but this works

Code: Select all

add-column:a a:=stuff(regexextract(fullpath:,".*?\\.*?\\(.*).{8}"),2,0,":")

I'm not so sure
BTW: stuff() ... completely forgot about its existence. Thanks for the refresher!

[sk2107]

that works nicely too .. thanks a lot

Best Regards