Problem with Auto Incrementing Text String

[WednesdayC]

Hi All

I am having difficulties with creating a formula to deal with the following:-

I am trying to autoincrement the last character (1, 2 or 3 characters) of a text string.

The problem is the string could be different lengths and needs to autoincrement from whatever the last number is from the cell in the row above.

So if the text string in say Cell A2 is

3/1/2018/XXXX/XXX1

<colgroup><col style="mso-width-source:userset;mso-width-alt:4827;width:132pt" width="132"> </colgroup><tbody>
</tbody>

Cell A2 should be

3/1/2018/XXXX/XXX2

<colgroup><col style="mso-width-source:userset;mso-width-alt:4827;width:132pt" width="132"> </colgroup><tbody>
</tbody>

However if the string in Cell A2 is, say

3/1/2018/XXXX/XXX16

then I require

3/1/2018/XXXX/XXX17

Or

3/1/2018/XXXX/XXX116

Then all the rows downwards should increment by 1, so the result in A2 will be

3/1/2018/XXXX/XXX117

I can't work out how to deal with this, due to the variable length of the strings. Also the Xs are only for the demo, these will really be numbers, but only the last 1, (2 or 3) (if the previous number is 10 or 100 upwards) needs to increment.

Is there a formula that can deal with this please.

Many thanks in advance and I hope I have been clear in my requirements.


Wednesday


<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style><style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl73 { color: windowtext; font-family: Georgia; }</style>

 

[WednesdayC]

Hi Rick

Thanks for the heads up.

My text would never look like the patterns you mentioned, so it is a perfect solution for me. I have tried it with all the possible permuations and it always works.

I am very grateful for your tremendous help.

Regards

Wednesday

 

[WednesdayC]

H jtakw.

Oops - so its pipe not pike. I will take a look at your revised formula, but Rick's formula seems perfect for my needs.

Yes, I see now my "pipe" character is in the same place - I have just never used it before.

Thanks for your help - I can see your formula suggestion being useful in other situations, with some tweaks.

Regards

Wednesday

 

[jtakw]

You're welcome.

A lot of times, we tend to "Over-Think" the problem and solution.

I just realized that's what had happened here, the Simplest solution is:

Select your subject cell with the starting Text string/number (i.e. A1), grab the Cell Handle - little square at lower right corner of cell - Drag down. That's it, no formula or UDF needed.

 

[Rick Rothstein]

I just realized that's what had happened here, the Simplest solution is:

Select your subject cell with the starting Text string/number (i.e. A1), grab the Cell Handle - little square at lower right corner of cell - Drag down. That's it, no formula or UDF needed.

I was going to post that originally, but in re-reading the OP's first post, I got the impression he might want to change the value in cell A2 and have all the cells under it automatically adapt to the change, hence, the OP's request for a formula solution. Of course, I understand that after cell A2 is changed, double-clicking that little square would automatically fill down the updated text and auto-increment the numbers at the same time (maybe I should have suggested it originally after all). Anyway, your suggest is still a viable one if the OP would like to adopt it.

 

[Peter_SSs]

If the OP is wanting a formula it seems that the existing suggestions have been acceptable, so there may be nothing in this but I was wondering if it is possible that the original value might end with something like this?
XXX001
If that is not possible then no need to read on.

If it is possible then what would be the desired result after ..XXX009?
Is it ..
a) XXX10 as per jtakw's formula and Joe's UDF, or
b) XXX0010 as per Rick's formula (it was shorter but not robust against row insertions above ), or
c) XXX010

If c), can the values ever get beyond XXX999?