Need help in getting the string between “\”

[Madhuri_G]

Hello team

need help in getting the string after two slashes in the below path - I mean need get the keyword of ‘Microsoft SQL server’

plug-in output:

path : C:\Program Files\Microsoft SQL Server\150\DT

I have hell no of rows and my mid function is not working well can anyone explain how to get this fixed

thank me n forward for your help

 

[ETAF]

will there always be a third /

do you have version 365v of excel - various TEXT functions available for that

how about
=TRIM(MID(SUBSTITUTE($A2,"\",REPT(" ",100)),200,100))

or will the string always start at the same place or do you have different examples

 

[p45cal]

=INDEX(TEXTSPLIT(A1,"\"),3)

?

 

[bosco_yip]

If your data always started in: "C:\Program Files\"

Then, you can try this:

In B2, formula copied down:

=MID(A2,18,FIND("\",MID(A2,18,99))-1)

 

[Madhuri_G]

will there always be a third /

do you have version 365v of excel - various TEXT functions available for that

how about
=TRIM(MID(SUBSTITUTE($A2,"\",REPT(" ",100)),200,100))

or will the string always start at the same place or do you have different examples

 

[ETAF]

??? you seem to have copied/quoted my post with no comments ????

 

[Madhuri_G]

Yes there are always be third \ as well.
Few worked and rest didn’t but anyways you’re rock ️

 

[ETAF]

Few worked and rest didn’t

what does that mean ?????

has your question now been answered??

if not more examples are needed

 

[Madhuri_G]

??? you seem to have copied/quoted my post with no comments ????

Some come with correct answers - I did manually in column P as service - the formula I applied in column N

I can’t able to apply full excel sorry mage is too sorry i

 

[Madhuri_G]

It did answered 80%

but some has path as below :

path\Users\dv2\BMC Software\Control

But I need third string ‘BMC Software’

 

[Madhuri_G]

The path is irrelevant some are in third position some are second so it’s a headache no worries

All good don’t stress much! Rest I’ll make manual adjustments, thanks a bunch have a great weekend

 

[ETAF]

third position some are second

any rules that could be applied to test if its 3rd or 2nd string needed

the 80% wrong - are they always \user\

C:\Program Files\Microsoft SQL Server\150\DT
so after the 2nd \

\Users\dv2\BMC Software\Control
after the 3rd \

maybe an IF combined with the index / textsplit
=INDEX(TEXTSPLIT(A3,"\"),4)

but a few more examples needed, and perhaps can be worked out to nearer 100% , if you want to - also you have not said what version of excel - so dont know if you have textsplit()

 

[Madhuri_G]

any rules that could be applied to test if its 3rd or 2nd string needed

the 80% wrong - are they always \user\

C:\Program Files\Microsoft SQL Server\150\DT
so after the 2nd \

\Users\dv2\BMC Software\Control
after the 3rd \

maybe an IF combined with the index / textsplit
=INDEX(TEXTSPLIT(A3,"\"),4)

but a few more examples needed, and perhaps can be worked out to nearer 100% , if you want to - also you have not said what version of excel - so dont know if you have textsplit()

I dint have textsplit in my version

 

[Peter Bartholomew]

This is from someone that deleted Excel 2010 as being unfit for purpose. Using 365

= MAP(list, Splitλ("\",3))

where MAP serves to feed the strings that comprise the list to the Lambda function 'Splitλ' one by one.

= LAMBDA(separator,instance,
    LAMBDA(string,
      INDEX(
        TEXTSPLIT(string, separator), ,
        instance)
    )
  )

 

[bosco_yip]

Then try this formula to extract string after 2 or 3 slashes :

In B1 enter formula copied down:

=SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," ","^"),"\",REPT(" ",50)),99,98))," ","</b><b>")&"</b></a>","//b[contains(., '^')]"),"^"," ")