GoogleDocs Spreadsheet: Add 1 to a String That Includes Letters, for Sequential Serial Numbers
GoogleDocs is awesome. There is nothing better than storing all of your spreadsheets, documents, forms, and presentations in an online secure location. Not only that but you can open and edit from any PC in the world at any time without having to worry about access, etc.
Today I was creating a spreadsheet on GoogleDocs to list account numbers and serial numbers that were sequential though they also contained letters. An example of the set of numbers I was trying to enter quickly is listed below.
Example List of Account Numbers: AB000001, AB000002, AB000003, AB000004…., AB003781, AB003782
In the example above I am trying to demonstrate that there were thousands of strings that are sequential even though they contain letters and numbers. Do do this I needed to create a formula in the GoogleDocs spreadsheet that would extract the number out of the row above and add 1. I ended up coming up with the below formula which after being created I copied it and pasted down the entire column.
GoogleDocs Spreadsheet Formula: =CONCATENATE(“AB”,RIGHT(A1, 6)+1)
This formula was first created in cell A2. Keep in mind my first account number was actually AB150001 so if the numbers portion of the account number actually started with zeros I would need to modify the formula because CONCATENATE will strip those out. The formula above does two things. The first is it combines the data again into a string using CONCATENATE. You can see that you could put any combination of data between quotes to add as a prefix or at the end of the string by adding another comma. So if the account numbers all ended with “CD” (example: AB150001CD) then you would make the formula look like this, “=CONCATENATE(“AB”,RIGHT(A1, 6)+1, “CD”)”. The next thing it does is strip out the right 6 characters from the cell above and then adds 1 to that number.
Like any good spreadsheet software GoogleDocs spreadsheet is really powerful and can do just about anything you want it to do as long as you are patient enough to figure it out.