Removing the first character (Q) from a cell

I've inherited a set of smartsheet quotation/order/purchase order sheets which I've slowly been enhancing over the last 2 years.

As a quote progresses to an order and then to a purchase order there is a reasonable amount of data which should be copied and pasted between sheets rather than re-entered manually. To aid this I'm preparing a new set of sheets to be released at the start of the next FY which will have all the columns etc in the same order.

I've run into an issue with our quote number however. On the quote sheet the quote number is autogenerated based on the following formula

=IF(ISBLANK(Date3), "", "Q" + IF(MONTH(Date3) < 10, RIGHT(YEAR(Date3), 2) + "0" + MONTH(Date3), RIGHT(YEAR(Date3), 2) + MONTH(Date3)) + IF([Serial1]3 < 10, "0" + [Serial1]3, [Serial1]3))

Date is manually entered, serial is autogenerated sequentially, and then we end up with eg: Q230115

When a quote becomes an order we drop the Q to indicate it is now a contract. As I copy and paste data from the quote sheet to the order sheet I would like the Q to be automatically removed. I've tried using right and replace but they require a new column which defeats the object. Is it possible??

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!