Quick problem - string mix of letters & numbers - increment by one

Just posting a quick problem I have. It shouldn't be too hard but I'm under the weather and can't work how to tackle it right now.

Say you have a string which is a reference number. It has alphabetical characters at the front of the string and numerals for the rest. e.g. "XYZ42567" I need a function that I can pass this string to and it will generate the next string. so from the example, the function would return "XYZ42568".

Also we can't guess what the letters are for, so if the input argument is "XYZ999" then an input box should be shown to the user that the function can't predict the next code so it will need it manually entered this time.


To make it easier, I currently only need it for strings that are in the format of letters followed by numerals.

(But ideally the function should accept a complete mix of numerals & letters. The function only increments the last block of numerals from the right).

is it always 3 letters?

Please Login or Register  to view this content.

if it is
assuming a1 is cell in reference

No. I only used 3 letters in the example.

So as you can see there are few little string tricks that will be needed here. e.g. Detect rightmost non-numeric & RIGHT & LEN etc.

How about just dragging down the fill handle?

array formula
shift+ctrl+enter

Please Login or Register  to view this content.

does not work if starts with numeric

extracting number from string found here
http://office.microsoft.com/en-au/ex...001154901.aspx

the $9 represents the number of characters so if over 9 increase

perhaps

Please Login or Register  to view this content.

@ shg & humdingaling = thanks for the suggestions but that is not what I am looking for because:
1. I don't need this function for an excel range and
2. I don't know how the end user deals with a part reference like "ABCXYZ999" + 1 (Is it "ABCXZA000"? is it "ABCXZA001"? is it "ABCXYZ1000"? I don't know. Not sure the end user is either! ) So the end user needs to be prompted if this happens.

@ JosephP = once again you provide the answer. +1


I'll mark this as solved but I'm uneasy about using vbscript through VBA. How compatible is this?

UPDATE: Below was solved by shg.
I'll rep anyone who can provide code that works similar to Josephs but using standard VBA methods only (e.g. LEN, RIGHT, MATCH etc).

it should work on windows versions of excel

Originally Posted by JosephP

it should work on windows versions of excel

It does.

I was thinking of Mac users.

I guess someone oughta ;-)

Please Login or Register  to view this content.

Thanks shg. +1

I don't know if it's an issue for what you're doing but shg's current version does not retain leading zeroes in the numbers

Thanks for pointing that out. I haven't come across any part or reference numbers with leading zeros before any numbers but I would like the function to handle it if it ever does.

The output string should be the same character length as the input string with the last set of numerics in the string incremented by one. Any leading zeroes in the last set of numerics in the string should be kept (to maintain string length). Where the input strings last set of numerics are all 9 then the function should return an input box to ask the user for a manual entry - see post #7 for the reasons on this.

I am thinking the detection part of could be solved by changing this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

(untested)

UPDATE: Below solved by shg
I'll rep anyone who can edit the code to achieve the outcome in the 2nd paragraph of this post. Thanks

Please Login or Register  to view this content.

That was quick!

I'll test it out and get back to you. (What happened to the input box? )

Originally Posted by shg

Please Login or Register  to view this content.

Close. I tested it and it only works with one leading zero. ("ABCXYZ0999" = works, "ABCXYZ00999" = fail)

Below is my current version of the code:

Please Login or Register  to view this content.

I tested it and it only works with one leading zero. ("ABCXYZ0999" = works, "ABCXYZ00999" = fail)

Please Login or Register  to view this content.

Those all look right to me.

I was testing the code in post #17 (uncomment the 3rd last line & comment the 4th last line) with these commands in the immediate window:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

- they both gave me the result of ABCXYZ1000

Dunno what to tell you.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Sorry shg. Your code was right all along. My apologies for wasting your time.
+1 for successful solving as stated in post #14.


(I was using my version of your code - see post #17 - and it wouldn't handle more than one zero. When I tried your version - post #20 - it worked. Then I realised. I had missed changing one variable. Lesson learnt = Never, never, turn off Option Explicit. I don't need to and it's not worth it! )

Thanks jindon. However I am avoiding using RegEx (see post #7).

My assumption, sorry. BTW I repped you for contributing this code in case it helps another forum user who wishes to use a RegEx version.

This is a little more robust:

Please Login or Register  to view this content.