Extracting text string after nth occurrence of a character in Excel (functions or VB)

Question

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 \?

Answer 1

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

Answer 2

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.