grab number from string based on some rules

[garyb123]

Hi Team

I have a column with thousands of string values like the one below. I need to get the number after the second semicolon.

So that would be 55033 in the example below, this number could be 5,6 or 7 characters, basically though i need the number between the second semicolon and the underscore.

AT_29&BI=22234&NP=123689582_0;60736_0!0;55033_609264142928019.000!0&DG=60766;0;1.000&NM=0&sfnew=&SS=5&RM=0

thanks
Gary

[AliGW]

Try this:

=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A1,";","|",2),FIND("|",SUBSTITUTE(A1,";","|",2))+1,10),"_",REPT(" ",99)),99))

[Rick Rothstein]

Here is another formula that should also work (same number of function calls as Ali's formula)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("_",A1,FIND(";",A1,FIND(";",A1)+1))-1),";",REPT(" ",99)),99))

And here is yet another way which uses one less function call...

=MID(REPLACE(A1,FIND("_",A1,FIND(";",A1,FIND(";",A1)+1)),999,""),FIND(";",A1,FIND(";",A1)+1)+1,9)

[garyb123]

Thanks Both - those options worked a treat