I'd need to split or extract only numbers made of 8 digits from a string in Google Sheets.

I've tried with SPLIT or REGEXREPLACE but I can't find a way to get only the numbers of that length, I only get all the numbers in the string!

For example I'm using

=SPLIT(lower(N2),"qwertyuiopasdfghjklzxcvbnm`-=[]\;' ,./!:@#$%^&*()")

but I get all the numbers while I only need 8 digits numbers.

This may be a test value:

00150412632BBHBBLD  12458 32354 1312548896            ACT inv 62345471

I only need to extract "62345471" and nothing else!

Could you please help me out?

Many thanks!

CodePudding user response：

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:

• 
  

  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.