Find a portion of text

[itsgrady]

I need to pull the text nested between the 5th and 6th "/" in a long string of text, I tried the following formula:

=MID(A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) + 1) + 1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) + 1) + 1) + 1) - FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1) - 1)

It is not pulling the portion of the text I need. Is there a simpler formula?

Thanks for any help.

 

[RoryA]

You could try:

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"/","</b><b>")&"</b></a>","//b[6]")

 

[itsgrady]

Some of the numbers pulling have a leading zero. Is there a way I can add a ‘ before the number to show the leading zero?

 

[RoryA]

Are they a fixed length?

 

[itsgrady]

Are they a fixed length?

Yes, it’s six numbers. Only some begins with a zero.

 

[RoryA]

Then you can wrap the whole thing in a TEXT function:

=TEXT(FILTERXML("<a><b>"&SUBSTITUTE(A1,"/","</b><b>")&"</b></a>","//b[6]"),"000000")