Removing the first character (Q) from a cell

Ian Smith 2017

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??

sharkasits

If you are moving all of the columns, can you just use the same formula without the "Q" to recalculate it?

Ian Smith 2017

Sadly not, on the order sheet there is no guarantee anything will be consecutive and the date a etc will be different, so that has to be a straightforward copy and paste.