# Substrings

Hello AppSheet,

I want to omit the first letter of a value in a list so I can convert it into a number and find the max value in the column. So in Java I would just use casting and substrings, but how do I do this in AppSheet?

Thanks.
Tiffany

Same concept, different names.
`NUMBER(LEFT([YourColumn], 1))`

Thank you. Can I use this same concept with a date value? For example getting the last two characters of the year in a date value?

Here is what I tried

Sure there is also a RIGHT() and a MID(), as well as functions for “casting” to any data type.

3 Likes

That is a very complex expression. What does agid[id number] look like?

An example of an ID number would be 20-R1000 where 20 is the last two digits of the year

Ok, so this is a column expression that looks at itself. The part after the “R”, is it always 4 digits? Is it just sequential increments as long as the year is the same? Are these last 4 digits supposed to reset when the year changes? That’s the part I’m having a hard time with.

1 Like

So the part after the R increments with user input. The year part is constant and stays the same until next year. Yes, the last 4 digits reset when the year changes. For now I will assume it’s at LEAST 4 digits but it can be more.

This is what comes up for initial value. It’s odd because I don’t know where the e) characters come from.

I fixed it the first two characters, but the trailing digits at the end are incorrect.

Try:

`````` IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0,
RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1,
RIGHT(TODAY(), 2) & “-R” & 1)
``````

IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0
Find all entries that start with this year and count them. If there are 0, then it is a new entry for a new year.

RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1

If other entries already exist this year, take the last two digits of today’s year, add “-R”, and then the hard part. Select all that entries that start with this year. Remove the prefixes, and then find the max number and add 1.

RIGHT(TODAY(), 2) & “-R” & 1)
If the count in the first step is 0, it must be a new entry and therefore can start at 1.

This implementation doesn’t care about the length of the digits after the R, which may be helpful if you are unsure of how many entries per year might be added. Hopefully I got all the parenthesis right…

Thank you. I will fix the parentheses and let you know if it works.

I fixed the parentheses and got this

This should have correct parenthesis.

``````IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number])))>0,
RIGHT(TODAY(), 2)  & "-R" & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number]),
RIGHT(TODAY(), 2)=LEFT([ID Number])), (RIGHT(TODAY(), 2) & "-R"), ""))+1,
RIGHT(TODAY(), 2)  & "-R" & 1)``````

It still says LEFT function is used incorrectly

OH! Forgot the stupid second piece of it!

IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0,
RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number]),
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”))+1,
RIGHT(TODAY(), 2) & “-R” & 1)

Did it on the right, gotta have the same params on the left.

Typos, typos. Freehanding code always makes life more difficult for myself.

``````IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0,
RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1,
RIGHT(TODAY(), 2) & “-R” & 1)``````

I will try reading through it and fixing it myself