Try this regular formula:
Formula:
=IFERROR(--TEXT(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH("_"&{0,1,2,3,4,5,6,7,8,9},
SUBSTITUTE(A1,"_","|",1)&"_0_1_2_3_4_5_6_7_8_9")+1),ROW($1:$7)))),"[<100000]\A;#000000"),"No Match")
Edited to include this variation that excludes numbers greater than 9999999:
Formula:
=IFERROR(--TEXT(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH("_"&{0,1,2,3,4,5,6,7,8,9},
SUBSTITUTE(A1,"_","|",1)&"_0_1_2_3_4_5_6_7_8_9")+1),ROW($1:$10)))),"[<100000]\A;[>9999999]\A;#000000"),"No Match")
Note: that formula gets tripped up if the 6 or 7 digit number begins with a zero.
Is that something you can work with?
Bookmarks