Search for ZIP code with leading zero

I am afraid I did not make use of script. :frowning:

All I did see below:

I do not believe it is possible to return display values using the query language.

You may need to manipulate your values once these are returned to your app.

But theen it is too late, I send a search number from app to sheets and need related numbers back in the app. And ZIP code can be 4 or 5 digits, up to country.

What is the difference from your code in post above
with my code.

Hello, may I ask question?
Your googlesheet with the 012345 had format 'text only' for the cell?


If you mean me, I did not set a format, so it was at default.

Yes and no :slight_smile: Thank you for the info

The Doit result for my seach field is '01' which is correct.
When I set cell format to text in g.sheets and do google search&replace for 01 also the text cell with 01234 is found.
The app search within the google.sheet works in a diffent way.?

Do you mean like this:


Tick Match entire cell contents?

No, nothing ticked / selected in the options.

If you do tick it, it will only match cell contents, and thus only return the cell with 01 in it.

The problem is that I have not only 01 in one cell, ZIP codes contain 4 or 5 digits, just starting sometimes with ZERO. When I tick exact match, I get no result for search'01' and '01*' :frowning:
I thing this sample shows quite good what I am looking for:

Just that the search should be zero one '01'.
Really annoing problem for me as some user already reported via email that a 01 search does result in empty search match.

If you are using gviz, use the LIKE operator

  • like - A text search that supports two wildcards: %, which matches zero or more characters of any kind, and _ (underscore), which matches any one character. This is similar to the SQL LIKE operator. Example: where name like fre% matches 'fre', 'fred', and 'freddy'. (Ensure you use the UriEncode block to properly encode the %)

or use starts with

I don't quite understand what you need, in this example you get the numbers that start with 0 and 01
.If you only want the ones that start with 0, remove the multiplier by 1 and see if it starts with zero.

borrrar_zip.aia (3.1 KB)

Thank you, I will lokk into it this evening (German standard time).
For your reference a snapshot of the google.sheet where the data is coming from for my app. The app should list entries selected by the B colum, PLZ (ZIP code).
I hope it helps and is understandable. :slight_smile:

borrrar_zip_v2.aia (3.7 KB)

Thank you. The try on the mobile was working. But with borrrar_zip_v2 the 01773 is only found when the search field TextBox1 contains the full 01773.
When a 0 (or 01) is in the TextBox, it should be returned alll ZIP codes with 0 (/ 01) then it is perfect.
(I am OoO now for 3 days and might not have the time to check the discussion regulary..)

Hello, within your v2 i replaced the select list item with compare text and thought in the first try it would work, but not all ZIP codes with leading 0 (e.g. 04-not) where found. Also search for other ZIP starting with 7 or 8 didn't work anymore. :frowning: Google sheet has format '00000' in the cells with leading '0' now. Text won't work.

Hello, in this sample, the '01' is not from a input field.
Could it be that the input field loose the 0 in front of the 1 at the transfer to the csv search.?
Sorry, I don't know how to explain better...

Your column B does not look like it is set to text/plain number format ?

Sorry, I don't know how to explain better...

Use pictures if words can't explain it :wink:

Setting in google sheets: