10

Looking for VB or Excel function to return

azat-tab-25mg

from

Y:\master-documentation\azat\dp\tab\25mg\2-drug-product\azat-tab-25mg-dp-1-bmi-[d-6475703-01-11].pdf

Is there a function to get the text after the 7th occurrence of \?

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
GaryWellock
  • 121
  • 1
  • 1
  • 8
  • Are you just looking to extract the filename i.e. everything after the *last* backslash? (i.e. not necessarily the 7th) If so, then [this answer](http://stackoverflow.com/a/5505006/119775) does this. And [this one](http://stackoverflow.com/questions/6133287/how-to-extract-the-last-substring-from-a-excel-column/6135343#6135343). – Jean-François Corbett Oct 21 '14 at 10:57
  • possible duplicate of [Get parent folder path from file path using cell formula](http://stackoverflow.com/questions/5504797/get-parent-folder-path-from-file-path-using-cell-formula) – Jean-François Corbett Oct 21 '14 at 10:58

4 Answers4

7

The split function splits a string into an array of whatever size is required. The first argument for Split is the text to split and the second is the delimiter.

Sub test()
Dim strFound As String
Text = "Y:\master-documentation\azat\dp\tab\25mg\2-drug-product\azat-tab-25mg-dp-1-bmi-[d-6475703-01-11].pdf"
strFound = Split(Text, "\")(7)

End Sub
gregV
  • 987
  • 9
  • 28
quantum285
  • 1,032
  • 2
  • 11
  • 23
5

If you have data in cell A1 the following worksheet formula extracts everything after the 7th "\"

=REPLACE(A1,1,FIND("^^",SUBSTITUTE(A1,"\","^^",7)),"")

SUBSTITUTE function replaces the 7th "\" with "^^" [use any character or combination of characters that you know won't appear in the data]

...then FIND function finds the position of "^^" and allows REPLACE function to replace those characters and all before with nothing.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • 1
    +1 Instead of `^^`, I suggest you use `?` (illegal in paths therefore certain it won't be there) or some non-printable character like `CHAR(1)`. – Jean-François Corbett Oct 21 '14 at 11:03
  • @Jean-Francois Corbett Yes, I knew "?" was illegal in filenames - wasn't sure about paths - that would be a good option here – barry houdini Oct 21 '14 at 11:15
  • still you're only half way there with your answer as the OP wants to return only a part of the *`azat-tab-25mg-dp-1-bmi....`*. Also I think this just adds extra overhead –  Oct 21 '14 at 11:18
  • 2
    @vba4all Agreed.....but he does say `"is there a mid function to get the text after the 7th occurance of \ ?"` - and the way to limit the text after that isn't defined, I assumed Gary knew how to do that already. – barry houdini Oct 21 '14 at 11:36
  • Old thread, hopefully you are still active. How would you reverse the polarity on this formula to return all the text *before* the nth instance of a character? Edit: nvm "=SUBSTITUTE(E2,REPLACE(E2,1,FIND("^^",SUBSTITUTE(E2,"/","^^",2)),""),"")"... elementary. – user2989297 Nov 11 '15 at 15:47
2
filename = Right(fullfilepath, Len(fullfilepath) - InStrRev(fullfilepath, "\"))

InStrRev finds the first occurrance of a string starting the search from the end.

jforberg
  • 6,537
  • 3
  • 29
  • 47
Simon
  • 21
  • 1
1

To return

azat-tab-25mg

from your original string, I returned everything from the last "\" up to the third "-" following.

Worksheet Excel function:

=LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)),
"-",CHAR(1),3),FIND(CHAR(1),SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(
A1,"\",REPT(" ",99)),99)),"-",CHAR(1),3))-1)

User Defined Function:

Option Explicit
Function Meds(S As String) As String
    Dim S1 As Variant, S2 As Variant

S1 = Split(S, "\")
S2 = Split(S1(UBound(S1)), "-")
ReDim Preserve S2(0 To 2)
Meds = Join(S2, "-")

End Function
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60