extract a substring between backslashes from paths

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I would like to extract the 'PM_xx' substrings, i.e. PM_4, PM_6 and PM_2_3 from the below Paths, using a formula if possible

The links have one thing in common.
Looking from the right: these are the substrings between the first and second backslash.

C:\Users\DMD\PM_4\IFU_marSeal_5_steril_90-012-59-11_REV_1-2_RD_2015-07_6s_A4_S220.pdf
C:\Users\PM_6\IFU_maxium_5_steril_90-048-73-11_REV_7_RD_2015-07_6s_A4_S17.pdf
C:\Users\DMD\Files\PM_2_3\IFU_pincers_90-073-59-14_REV_2_RD_2015-07_6s_A4_S9.pdf

Help is much appreciated. Thank you very much in advance.

I have attached a sample file for your convenience.

Regards, Andreas

extract_substring.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

pls try this UDF
Function CellRegexSimple(Myrange As String) As String
      Dim regex As Object
      
      Set regex = CreateObject("VBScript.RegExp")
      Dim strInput As String


        strInput = Myrange
    
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\\(PM[\d_]*?)\\"
        End With
        On Error Resume Next
        Set Matches = regex.Execute(strInput)
        strRes = Matches(0).submatches(0)
        On Error GoTo 0
        CellRegexSimple = strRes
      Set regex = Nothing
End Function

Open in new window

Regards
extract_substringV1.xlsm
Top Expert 2016
Commented:
Or pls try
=MID(A2,FIND("\PM",A2)+1,FIND("\",A2,FIND("\PM",A2)+1)-FIND("\PM",A2)-1)

Open in new window

Regards
Andreas HermleTeam leader

Author

Commented:
Oh great Rgonzo, how quick you came up with this and it works just fine. Thank you very much for it.

If you do not mind, I will wait some more time, maybe some expert or you also come up with a formula approach.

Anyhow, a real superb job from your side. I really appreciate it. Regards, andreas
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Andreas HermleTeam leader

Author

Commented:
uppps, here we are, our posts crossed, will test it right away ... :-)
Top Expert 2016

Commented:
Edited (a bit more precise)
=MID(A2,FIND("\PM_",A2)+1,FIND("\",A2,FIND("\PM_",A2)+1)-FIND("\PM_",A2)-1)
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may also try this....
Assuming your string is in A2 then

=TRIM(MID(SUBSTITUTE(A2,"\",REPT(" ",LEN(A2))),LEN(A2)*3,LEN(A2)))

Open in new window

Andreas HermleTeam leader

Author

Commented:
Hi Rgonzo, I am really deeply impressed, how come you come up so fast with all these solutions. It is really incredible, I just do not get it :-)

A great job from your side, as always. Thank you very much for your professional help. I really appreciate it.

Regards, Andreas
Top Expert 2016

Commented:
@Neeraj the PM Folder is not allways  at the same level
Andreas HermleTeam leader

Author

Commented:
Upps, Subodh's and Rgonzo's post have crossed my post. I have already awarded the points. Subodh: will test your code and then let you know. Thank you very much for your great help.

Andreas
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
It's okay Andreas. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial