1

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
Nick m
  • 35
  • 6

1 Answers1

1

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • no luck unfortunately, still gives out the same input, am i not entering it correctly?should i apply it as an array formula or rearrange the formula at all? thank you for your help. you can use this link for the list of reference. https://stackoverflow.com/questions/74199593/google-sheets-transpose-irregular-column-data-in-groups-into-rows – Nick m Feb 02 '23 at 22:27
  • 1
    @Nickm can you share a copy/sample of your sheet? – player0 Feb 02 '23 at 22:31
  • its the table from the link i posted and im trying to pull the number after company ex 70000123456, 70000987654 – Nick m Feb 02 '23 at 22:38
  • 1
    @Nickm updated, try now – player0 Feb 02 '23 at 23:24
  • 1
    worked, much better than anything i've tried, thank you – Nick m Feb 03 '23 at 14:51