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>

 

[Joe4]

Here's a formula solution for what you described:

I had an idea that a formula solution would be kind of messy, and not real flexible.

Note the revised condition:

The format will either be slashes or hyphens.

So, we have to account for the fact that some entries may use hyphens (dashes) instead of slashes.
I cannot imagine what a formula solution accounting for both of those possibilities would look like!

When things get this complex, I like the User Defined Function solutions (if they can use VBA), as I believe it is a lot easier to read and maintain when you have complex conditions.

 

[jtakw]

Note the revised condition:

So, we have to account for the fact that some entries may use hyphens (dashes) instead of slashes.
I cannot imagine what a formula solution accounting for both of those possibilities would look like!s.

It looks like the formula in my Post #10 , look again, sample in Column D.

 

[Joe4]

It looks like the formula in my Post #10 , look again, sample in Column D.

Ah yes, there it is. Sorry I missed that.
Nicely done, and a good solution for those who cannot use VBA.

 

[jtakw]

Thank you, Joe4.

 

[WednesdayC]

Hi jtakw

I am so sorry for the delay in replying to you. I have been away, and hadn't realised that anyone else had replied.

The formula works great, although I am now using Joe's UDF, but may switch to this.

I must admit I don't understand the formula, as it seems to be finding something that doesn't exist - i.e. the pipe character.

If you have time, please can you explain briefly how it works.

Either way, thanks a million, it is just the job.

Regards

Wednesday

<tbody>
</tbody>

<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; }.xl63 { font-family: Calibri; }</style>

 

[WednesdayC]

Hi jtakw

Having looked at your formula in greater detail, I realise I am being a bit of a dunce. I see the "/" and "-" are subtituted for a pike character (I don't know where this is on the keyboard)!.

It is very clever!

Once again, thanks a lot

Regards

Wednesday

 

[Rick Rothstein]

Here is another (shorter) formula that I think should also work..

[table="width: 500"]
[tr]
	[td][FONT=Arial]=LEFT(A1,LEN(A1)-LEN(LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99)))))&LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99)))+1[/FONT][/td]
[/tr]
[/table]

Note: The above formula is completely independent of the shape of the the value in cell A1... all it requires is that the value end with a number in order to work.

 

[WednesdayC]

Hi Rick

Wow, this formula is great. I am going to switch to this one.

How do you come up with this stuff!!!

Thanks to all of you. You are all so helpful.

Regards

Wednesday

 

[Rick Rothstein]

Hi Rick

Wow, this formula is great. I am going to switch to this one.

I said earlier that my formula was "completely independent of the shape of the the value in cell A1". That is not entirely true. If the end of your text could ever be a number followed by the letter "e" (either upper or lower case) followed by the ending number or if the end looked like a date (for example, abcd 1/2 or abcd3/4/5), then my code will fail to produce the correct answer. As long as neither of these are true, my formula will work fine.

How do you come up with this stuff!!!

With the exceptions cited above in mind, LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99))) will find the last number in a text string. I simply used its length to find everything before it and concatenate that onto the same expression increased by one.

 

[jtakw]

Hi jtakw

Having looked at your formula in greater detail, I see the "/" and "-" are subtituted for a pike character (I don't know where this is on the keyboard)!.

It is very clever!

Once again, thanks a lot

Regards

Wednesday

You're welcome.

The "Pipe" character is directly beneath the "Backspace" on my keyboard, don't know about yours.
There's no particular reason to use the Pipe, sometimes I use @, #, ^, or whatever I "believe" mostly likely won't be in the Original string.

Also, I realize the formula can be a touch shorter:

=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1,"-","/"),"/","|",4))+3)&MID(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1,"-","/"),"/","|",4))+4,255)+1