Spreadsheet question about incrementing an alphanumeric string (Google Sheets)

Factual Questions
1

Hey folks! I’m hoping someone here can help me out with a confusing (to me) spreadsheet question. I can do most basic operations in Excel or Sheets, but this is a bit trickier, for me at least.

I maintain a spreadsheet of codes that I issue to music producers and artists that I work with as a mastering engineer or publisher. They’re called ISRC (International Standard Recording Code) and every time I master a track (prepare it for release/distribution) I assign that particular recording a code. This code is basically the social security number of the recording - it’s the key identifier for tracking the recording for purposes of streaming and digital sales/use figures etc.

These codes follow a particular format, and as a licensed ISRC Manager, I generate them for third parties. After I do this, I need to maintain a record of the codes issued with some basic provenance and ownership/POC information for the US ISRC Agency. So far so good.

The problem is this: I need to increment the code numbers in a column. But it’s a string, not a numeric value. Basically the format is
XXYYYZZ00000
where XX is the country code, YYY is my registrant identifier, ZZ is the year of issue (i.e. 16 for 2016), then a five digit numeric designation for the unique audio file.

XXYYY don’t ever change. ZZ only changes at the new year. But each new code I issue has a different numeric. They don’t have to be fully sequential, and I often block out a number range for a particular artist or client, from say 00100 to 00199.

So here’s a sample code:
QM9EM1400452
The next one is 453, then 454 etc in this block.

A block for a different artist might be
QM9EM1401600 to 1699 or what have you.

How can i set it up so that I have a column that automatically increments the last digits as I enter new songs in the sheet?

Currently I have to manually change each one as I paste the previous value in. It’s very slow and tedious. I know there’s a better way that probably concatenates a string and an integer as we create a new row, but damned if I know how to do it.

Thanks in advance for any assistance. These codes aren’t secret or anything so if it helps I can share a sample of the actual sheet.

Cheers!

2

And a sorta but not really related question:

I have a long block of codes for which I need to change the year designated - they were blocked out last year but never used. So I need to change several hundred from (ex.) QM9EM152201 to QM9EM162201.

So the only digit I need to change is the single year, from 5 to 6. Although I guess it’s probably better to change it from 15 to 16 because that is a two digit variable and the complete value.

Anyway this would be a huge time sink to manually change (hence why I’ve avoided it til now) but I bet there’s a way to split the string up and increment only the year variable, then moosh it back together. But it’s beyond me.

Any thoughts or solutions?

3

I’m not an Excel guru, but here’s how I would do it in Google Sheets, which has a similar set of functions. Suppose A1 has QM9EM1400452, then put this formula in A2:



=REPLACE(A1, 8, 5, TEXT(VALUE(RIGHT(A1, 5)) + 1, "00000") )

That grabs the rightmost 5 characters of A1, casts it to an integer, adds 1, then casts it back to a string with the format “00000” (five chars, leading zeroes), then finally uses that new string to replace the five characters of the value in A1 starting at position 8.

This formula works and can be duplicated down the column. Hopefully it should be pretty easy to translate that to Excel.

You can use similar functions to split the existing erroneous codes into substrings to tweak the year, but figuring out how to apply it to only the broken ones might be a bit of a challenge.

Edit: Looks like you posted this in the Great Debates forum. I’ve asked the mods to move it to General Questions for you.

4

And so it shall be.

5

I think your string and integer method would work fine. Set up the string in one otherwise unused column. Set up the integer in the next, using a function to make each entry one larger than the next. Then simply set your ISRC to be the concatentation of the two fields.

6

Thanks guys! I think that will work great, and I even mostly understand the syntax of the function.

Sorry about the forum, bad eyes and fat fingers…

I appreciate the help!

7

For this one, since I assume the syntax is always the same number of digits, you can do Data - Text to Columns, “Fixed Width” and separate the strings into three columns: 5 chars, 2 chars, 5 chars. Choose Text format so the last five numbers don’t show up as numbers only (e.g., 00011 would become 11 if you don’t specify Text). Update just the year, and then CONCATENATE the three columns back together.