Question

Hello, I have a primary column that starts with 21 for the year and then TMP for the type of advertisement.  When someone submits an ad request, the default value to the sheet is 21TMPXXXX; however, we can no longer see the ads in numerical order since we reached 1000... the 900's end up all over the place...I would like to have a formula that ignores the 21TMP and only looks at the either the 3 digit or 4 digit number to allow us to sort (descending preferred) so the newest ad is always on the top of the sheet...  Note, we assign the ad the missing digits when we receive it (it is NOT automated).

The primary column will have a mix of 21TMPXXX and 21TMPXXXX ... we did not realize we would reach over 999 assignments in the 2021 :( in 2022, I'm hoping we won't have this issue since we are starting with XXXX digits.  But if anyone has a better suggestion, I'm definitely open to hear it.

Thank you!!!!

Kelly Moore · Accepted Answer

Hi @DHR Temp Assignment Team

Because you have a set beginning pattern to your data, the REPLACE function is one approach to your problem.  The REPLACE function (vs Substitute) will easily accommodate different Year prefixes because it looks at length of string, not what the string contains. As written the formula says it will replace the first 5 characters and write over them with a blank.

You will place this formula in a helper column- this new helper column will be what you can then sort.

=REPLACE([your primary column]@row, 1, 5, "")

Don't forget to insert your actual column name into the formula.

cheers

Kelly

https://help.smartsheet.com/function/replace