Extract a text string after the nth occurrence of a character in Excel (function or VB)

Question

Search 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 text after the 7th occurrence of \ ?

Answer 1

The split function splits a string into an array of any size. The first argument to Split is the shared text, and the second is the delimiter.

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

Answer 2

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

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

Function

SUBSTITUTE replaces the 7th "\" with "^^" [use any character or combination of characters that, as you know, will not be displayed in the data]

... then the FIND function finds the position "^^" and allows the REPLACE function to replace these characters and everything before that with nothing.

Answer 3

 filename = Right(fullfilepath, Len(fullfilepath) - InStrRev(fullfilepath, "\")) 

InStrRev finds the first occurrence of a string that starts the search from the end.

Answer 4

To return

Azate Tabs-25mg

from your original line, I returned everything from the last "\" to the third "-" below.

Excel worksheet 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) 

Custom 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