Search code examples
google-sheetsgoogle-sheets-formulatransposetrimregexp-replace

Where to nest trim into a google sheet formula?

I am stuck trying to find how to put trim into a function to remove blank answers from a query on a column of cells

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

got me to extract the numbers from a mix of numbers and letters in a list but has left

Column A
12345678912
12345678912

id like for it to pull up and end up like

Column A
12345678912
12345678912
Solution

  • try:

    =FILTER(N:N, LEN(N:N)=11, NOT(ISDATE_STRICT(N:N)))

    enter image description here