WednesdayC
Board Regular
- Joined
- Nov 7, 2010
- Messages
- 201
- Office Version
- 2016
- Platform
- 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>
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>