[Solved] Split string from right end

[morphingstar]

Split these from the right side at the position of the first found "\". The "\" may stay with the left string. Separate file name from path in WIN10.

F:\antarctica\Antarctica - once a tropical paradise - Peter Mungo Jupp full version google MUNGOFLIX.mp4
F:\antarctica\David Wilcock - Antarctic Atlantis - The Most Defining Moment in Human History.mp4
F:\antarctica\salla_antarct\_Volcanoes melt Antarctica, 50 mile wide alien city revealed - Race is on!.mp4
F:\antarctica\salla_antarct\Biggest Antarctica Mysteries That Defy Explanation [New Video].mp4
F:\Geo\TechnModr\__Neueste Technik - Die Neuen Antriebskonzepte 2018 _Dokumentation HD_.mp4

These file paths include an undefined number of folders, only the last one "\" on the right end may activate the separation.
I do not see how it can be done, though splitting a file path seems to be a useful function.
I am inexperienced with macros, avoid it.

Any ideas? Tks.

[Zizi64]

Is it what you needed?

Split.ods

(15.37 KiB) Downloaded 105 times

[RusselB]

Combining

Code: Select all

=FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))

from this topic with the MID function.
My suggestion for a final code, keeping the last \ would be

Code: Select all

=MID(A1;FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))));LEN(A1))

If you don't want the last \ then use

Code: Select all

=MID(A1;FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))+1;LEN(A1))

Re-reading your post, and looking at Zizi's attachment, makes me wonder which you want.
You say that you want the filename separated from the path, which means that you want it from the last \
But you also say that you want it from the first \
Which means that you are only removing the drive letter associated with the file. If that is what you want, then using a simple

Code: Select all

=mid(A1;3;len(A1))

will give you what you have asked for.

As noted, there is a discrepancy in your request, thus you now have options for both.

[Lupp]

Let me interpret the task as "How to get the filename (including the extension if any and having appended a query or a fragment part) from a pathname of arbitrary length.
(If you actually also need to get the split-off folder pathname, you can do so using SUBSTITUTE().)

As already shown you can do it without relying on user code.
However, Basic and the API as well offer appropriate means:
The Split() function and
the UnoService "com.sun.star.util.URLTransformer" applicable to a "com.sun.star.util.URL" structure respectively.
(See https://api.libreoffice.org/docs/idl/re ... _1URL.html for details concerning the API.)

Concerning the solution based on standard functions you may study the attached example using slightly different ways as compared with the above post. The soultions are based on a slightly more elaborate usage of regular expressions.

[Lupp]

A few additional remarks:

If you need (or urgently want) to disable regular expressions in functions you cannot use SEARCH() for the purpose as demonstrated in the attachment to my previous post.
However you then can
-1- Still use the REGEX() function as demonstrated in LibOO V 6.2 +.
-2- Count the backslashs using SUBSTITUTE, then replace the last one by a specific unambiguous string, and finally find its position using FIND(). If you want to do all this in one go, you get such a clear and self-explanatory formula like

Code: Select all

=MID(A1;FIND("\\";SUBSTITUTE(A1;"\";"\\";LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))+1;65535)

Too convoluted?
Well you may be ready then to ask for a user function. As mostly I wouldn't write one for the highly specialized purpose - or use the means already mentioned. For frequent usage in different6 situations somethink like

Code: Select all

Function reverseString(pS As String)
REM May be a bit slow for long strings due to Basic laziness.
If pS="" Then
  reverseString = ""
  Exit Function
End If
  Dim n As Long, k As Long, rS As String
n   = Len(pS)
rS  = ""
For k = n To 1 Step -1
  rS  = rS & Mid(pS, k, 1)
Next k
reverseString = rS
End Function

should be preferrable.
Now you can calculate the position of the last "\" in your original pathnames based on the position of the first "\" in the reverted string:

Code: Select all

=LEN(A1)-FIND("\";REVERSESTRING(A1))+2

...
One relevant shortcoming: The function as given above cannot be use for arrays of strings in formulas for array-evaluation

[morphingstar]

to Zizi64 » Tue Jun 04, 2019 12:12 am
thanks, your example
1. splits from left, requested was FROM RIGHT
2. Your example removes the drive name only, not the complete path which ends in "\".
3. The file name is wanted.
thanks

[morphingstar]

Someone referred a file from Villroy, sorry I don't see it no more.

Anyway, the result is fine with me and might help to simplify the offered answers.
Thanks to all

[RusselB]

the position of the first found "\".

That is the part that Zizi was working from. The search goes from left to right, so the first encounter is the one after the drive letter. The remainder is the split from the right of that location.

[Zizi64]

Split these from the right side at the position of the first found "\".

to Zizi64 » Tue Jun 04, 2019 12:12 am
thanks, your example
1. splits from left, requested was FROM RIGHT

The FIRST means for me: the first one from left. You meant the LAST "\" character in the string.

3. The file name is wanted.

There is such function in the Tools library of the macros. You can write your own Cell function based on the Tools Library.

[Lupp]

The solution demonstrated in the attachment above by the Original Questioner is (imo) not recommendable. It is too complicated, and it doesn't show a way to get a solution independet of whether RegEx in formulae are enabled.
See reworked attachment.