We help IT Professionals succeed at work.

extract a substring between backslashes from paths

62 Views
Last Modified: 2017-03-20
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

CERTIFIED EXPERT
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
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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
Andreas HermleTeam leader

Author

Commented:
uppps, here we are, our posts crossed, will test it right away ... :-)
CERTIFIED EXPERT
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
CERTIFIED 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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
It's okay Andreas. :)