need to extract number string from text string.

[allivant]

There is one column that includes all sorts of text and numbers. There isn't a common pattern throughout the column except a 16 number string that I want to extract and place in the next column. I've seen formulas such as:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$19),1)),0),COUNT(1*MID(A1,ROW($1:$19),1)))

I'm not sure exactly how to use this formula. I thought I knew what $19 meant but I tried replacing ROW($1:$19) with ROW("$1:"&LEN(A1)) but that did not work.

Example:
TEXTTEXT1234567891029384TEXT
TEXT1234567891029384TEXTTEXT
1234567891029384TEXTTEXTTEXT

 

[NBVC]

Try it like:

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$19),1)),0),COUNT(1*MID(A1,ROW($1:$30),1)))

you must confirm it with CTRL+SHIFT+ENTER not just ENTER as it is an ARRAY Formula.

 

[allivant]

I think I got it:

=MID(A5,MATCH(TRUE,ISNUMBER(1*MID(A5,ROW(INDIRECT("$1:"&LEN($A5))),1)),0),COUNT(1*MID(A5,ROW(INDIRECT("$1:"&LEN($A5))),1)))