Adding text to a number column in Google Sheet

[David_B]

Is there a way to add text to a number column in my Google Sheet?
I have the Item # auto generated using (MAX(MainTable, [Item]) + 1)
I’d like the App to add the word Item in front of each number in my Google Sheet if that’s possible.
The field has to stay a number field in order for the auto generating formula to work.
If the word Item can be added in front of each number then a user can search for Item 5, etc.
As it is I can search for 5 and it will bring up item 5, but it will also bring up any item that has a 5 in it.
IE. 9/25/2019 14:53:06
But, I need to be able to search for particular items by it’s item number.

[Suvrutt_Gurjar · Accepted solution]

Hi @David_B,

Please explore if following workaround helps.I believe the appuser will search the item number in only summary view. So you may wish to create a VC with an expression similar to

CONCATENATE(“Item”," ",[Item])

You may then have a CONTEXT(“View”) expression in this VCs’ show_if to display it only in summary views. Then you may search on this field with word “Item” prefix.

Please note that if the user uses even slightly different pattern to search say Item5 instead of correct format Item 5 or even Item 5 ( double space after prefix) the search will not work.

Hope this helps.

Also could you you please mention if Item # is a key field in the said table?

[David_B]

Sorry to bring this up again but I have encountered another bug in this.
While using CONCATENATE(“Item”," ",[Item]) works GREAT. I now have 53 items logged.
So doing a search for Item 2 brings up 2 but also 20 - 29. Is it possible to only search for an exact number? I could live with it as it is, but that would be nice.

[Suvrutt_Gurjar]

Hi @David_B,

As per my understanding, currently the search may not be able to be fine tuned to that extent.

[David_B]

I understand. What I find strange is when doing a search for item 2 it does not bring up 32, 42 or 52. Just 2 and all the 20’s.
Thank you for your time,

[Suvrutt_Gurjar]

Hi @David_B,

Your observation is correct. As per my understanding, it is ordered pattern for search. So when one searches for 2, anything that follows two will appear 2, 20-29, 200-299 and so on. In 32, 42, 52, the digit 2 follows other digits and thus does not appear.

For your requirement, you may wish to try the following revised workaround, though it is not elegant.

Please have an expression like CONCATENATE(“Item”," “,[Item],”*")

While searching, you will now need to suffix your search number with a " * ", for example “Item 2*” or “Item 21*”

[David_B]

Thanks, that does work and since it is only affecting single digit searches should work great.
The * does not seem to be needed for items above 9.

[Suvrutt_Gurjar]

Hi @David_B,
Yes, correct. I believe since your current item numbers are limited till 53 , you may not need that * workaround.

If your item numbers are going to exceed 99 , you may face that mutiple result challenge. A search for 1 will bring 1, 10-19 and 100-199, a search for 10 will bring 10, 100-109 for example.

[David_B]

Thanks, I will keep that in mind when I get that far.

[David_B]

Bless you, that worked like a charm.
And, yes Item is a key field.

[Suvrutt_Gurjar]

Hi @David_B,
Thank you for the update. Good to know the approach works the way you want.

On key, presume you have already evaluated the impact of using a serial number key generation system 's challenges in a multi user app environment.

Just in case not, you may wish to go through help article titled " What is a key" on AppSheet’ help document section.

[David_B]

I will, thanks.