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 \?
4 Answers
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 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.
filename = Right(fullfilepath, Len(fullfilepath) - InStrRev(fullfilepath, "\")) InStrRev finds the first occurrance of a string starting the search from the end.
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 
