Extract multiple substrings of numbers of a specific length from string in Google Sheets

Using this on a cell worked just fine for me:

(cell_with_data)=REGEXEXTRACT(A1,"[0-9]{8}$")

If you only need to do this for one cell (or you have your heart set on dragging the formula down into individual cells), use the following formula:

=REGEXEXTRACT(" "&N2&" ","\s(\d{8})\s")

However, I suspect you want to process the eight-digit number out of all cells running N2:N. If that is the case, clear whatever will be your results column (including any headers) and place the following in the top cell of that otherwise cleared results column:

=ArrayFormula({"Your Header"; IF(N2:N="",,IFERROR(REGEXEXTRACT(" "&N2:N&" ","\s(\d{8})\s")))})

Replace the header text Your Header with whatever you want your actual header text to be. The formula will show that header text and will return all results for all rows where N2:N is not null. Where no eight-digit number is found, null will be returned.

By prepending and appending a space to the N2:N raw strings before processing, spaces before and after string components can be used to determine where only eight digits exist together (as opposed to eight digits within a longer string of digits).

The only assumption here is that there are, in fact, spaces between string components. I did not assume that the eight-digit number will always be in a certain position (e.g., first, last) within the string.

Try this, take a look at Example sheet

=FILTER(TRANSPOSE(SPLIT(B2," ")),LEN(TRANSPOSE(SPLIT(B2," ")))=8)

Or this to get them all.

=JOIN(" ,",FILTER(TRANSPOSE(SPLIT(B2," ")),LEN(TRANSPOSE(SPLIT(B2," ")))=8))

Explanation

• SPLIT with the dilimiter set to " " space TRANSPOSE and FILTER TRANSPOSE(SPLIT(B2," ") with the condition1 set to LEN(TRANSPOSE(SPLIT(B2," "))) is = 8

• JOIN the outputed column whith " ," to gat all occurrences of number with a length of 8

• Note: to get the numbers with the length of N just replace 8 in the FILTER function with a cell refrence.

Please use the following formula for a single cell.
Drag it down for more cells.

=INDEX(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(SPLIT(REGEXREPLACE(A2&" ","\D+"," ")," "))=8,
                                        SPLIT(REGEXREPLACE(A2&" ","\D+"," ")," "),"")),"where Col1 is not null ",0)))

Functions used:

• QUERY
• INDEX
• TRANSPOSE
• IF
• LEN
• SPLIT
• REGEXREPLACE