Why is this Query statement not displaying "text" formatted phone numbers" on Google Sheets?

user_11832197 0
Viewed 40

This formula is returning eveything right,but the phone numbers, which are preceded by an apostrophe. If I happen to delete the apostrophe, it displays the numbers, but since there may be a 0 at the beginning, it has to come as text.

Here's the formula:

=iferror(QUERY('Form Responses'!$A$4:$P; "select N, B, C, D, E, F, G, H, I, J, K, L, A where N >= 1 and todate(A)=date '"&TEXT(B3;"yyyy-mm-dd")&"' order by N Asc");"Pick a date")

Here's how the data looks: enter image description here

...and this is the result right now: enter image description here

Here's the link to an example: https://docs.google.com/spreadsheets/d/1vxCwfcXRrpuvmkhpRsFrKQSq5aMjkdqlUYY-6_UknuE/edit?usp=sharing

Appreciate your time/help - as usual!

google-query-language google-sheets-formula google-sheets string-formatting substitution
1 Answers

try:

=INDEX(SUBSTITUTE(IFERROR(QUERY(
 {'Form Responses'!A4:P\ TEXT('Form Responses'!E4:E; "\×0")}; 
 "select Col14,Col2,Col3,Col4,Col17,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col1 
  where Col14 >= 1 
    and todate(Col1) = date '"&TEXT(B3; "yyyy-mm-dd")&"' 
  order by Col14 asc"); "Pick a date"); "×"; ))

update:

try:

=ARRAYFORMULA(TEXT(SUBSTITUTE(IFERROR(QUERY(
 {'Form Responses'!A4:P\ TEXT('Form Responses'!E4:E; "\×0")}; 
 "select Col14,Col2,Col3,Col4,Col17,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col1 
  where Col14 >= 1 
    and todate(Col1) = date '"&TEXT(B3; "yyyy-mm-dd")&"' 
  order by Col14 asc"); "Pick a date"); "×"; ); 
 {"@"\"@"\"@"\"@"\"\0#"\"@"\"@"\"@"\"@"\"@"\"@"\"@"\"mm/dd/yyyy"}))

enter image description here

user_56326290
Related