Problem with Auto Incrementing Text String

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
201
Office Version
  1. 2016
Platform
  1. MacOS
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 [TABLE="width: 132"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:4827;width:132pt" width="132"> </colgroup><tbody>[TR]
[TD="class: xl73, width: 132"]3/1/2018/XXXX/XXX1
[/TD]
[/TR]
</tbody>[/TABLE]

Cell A2 should be [TABLE="width: 132"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:4827;width:132pt" width="132"> </colgroup><tbody>[TR]
[TD="class: xl73, width: 132"]3/1/2018/XXXX/XXX2
[/TD]
[/TR]
</tbody>[/TABLE]


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>
 
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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.
 
Upvote 0
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
[TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"][/TD]
[/TR]
</tbody>[/TABLE]
<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>
 
Upvote 0
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
 
Upvote 0
Here is another (shorter) formula that I think should also work..

Code:
[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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top