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
-
If you are moving all of the columns, can you just use the same formula without the "Q" to recalculate it?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!