# 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