How do i find the 5 numbers to the right of the nth separator

[picto]

Hi

I hope someone can help

I have a flat file with 1000 plus lines after the 3rd separator is a 2 digit number and after the 9th is 5 digit number. the length of the other variable varies.

I want to extract those numbers to the next column.

I understand loops so can build a loop just cant get my head around extracting from a variable place

eg
12/32/12//3/trtr/545454/76/tr6/6565/12345/00 need to extract 12 and 12345
1/0/34/0/ww///uy/5/54321/0 this row is 34 and 54321


thanks
Shaun

 

[Andrew Poulsom]

You're not quite right in your description, but try:

=MID(A1,FIND("#",SUBSTITUTE(A1,"/","#",2))+1,FIND("#",SUBSTITUTE(A1,"/","#",3))-FIND("#",SUBSTITUTE(A1,"/","#",2))-1)

=MID(A1,FIND("#",SUBSTITUTE(A1,"/","#",10))+1,FIND("#",SUBSTITUTE(A1,"/","#",11))-FIND("#",SUBSTITUTE(A1,"/","#",10))-1)

If you want to coerce the result into a number add zero.

 

[Legacy 185660]

Will the first number always be 2 digits and the last always be 5?
If so I can make a fix for you, I tried andrews and it didnt work

 

[c_m_s_jr]

Couldn't you use text to columns? Use the delimiter "/" and then only pick the columns you want to keep?

 

[Andrew Poulsom]

Will the first number always be 2 digits and the last always be 5?
If so I can make a fix for you, I tried andrews and it didnt work

My formulas worked with the first example. For the second example I had to add another / separator. The OP said after third and ninth, but it's after second and tenth.

 

[Legacy 185660]

My formulas worked with the first example. For the second example I had to add another / separator. The OP said after third and ninth, but it's after second and tenth.

Oh! I tried it and it didn't work for me because it only showed the first 2 digits.
so right now it will show "xx and xxxxx" you can change the " and " in the middle to whatever separator you want.

This one also works regardless of whether is 9 or 10 just so long as the first number is 2 digits, and has 2 "/"'s in front, and the second is 5 digits and has 2 "/"'s from the end :D

=(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",2))+1,2))&" and "&(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",(LEN(A21)-LEN(SUBSTITUTE(A21,"/",""))-1)))+1,5))

 

[Legacy 185660]

Oh! I tried it on the second and it didnt work.

Here are some updated ones:

Together (xx and xxxxx)
=(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",2))+1,2))&" and "&(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",(LEN(A21)-LEN(SUBSTITUTE(A21,"/",""))-1)))+1,5))

First 2 (xx)
=(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",2))+1,2))

Last 5 (xxxxx)
=(MID(A21,FIND("#",SUBSTITUTE(A21,"/","#",(LEN(A21)-LEN(SUBSTITUTE(A21,"/",""))-1)))+1,5))

 

[picto]

Thanks, thats a great help