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]

Is the format always going to end with a slash, followed by three characters (numbers), followed by the number that you want to increment?

 

[Joe4]

Also, are you open to use creating a function in VBA that we can use on the sheet?

 

[Joe4]

If my assumptions are right, and you can use VBA, here is the Custom function I came up with:

Function NextNum(entry As String) As String

    Dim lastSlash As Long
    Dim prefix As String
    Dim num As Long
    
'   Make sure there is an entry
    If Len(entry) = 0 Then
        NextNum = ""
        Exit Function
    End If
    
'   Find last slash in entry
    lastSlash = InStrRev(entry, "/")
    
'   Get prefix
    prefix = Left(entry, lastSlash + 3)
    
'   Get number to increment
    num = Right(entry, Len(entry) - (lastSlash + 3)) + 1
    
'   Build next number
    NextNum = prefix & num

End Function

After putting this in VBA (in a General Module in your workbook), you can use it like any other function.

So, if you have your initial value in A2, place this formula in A3 and copy down for all rows:
=NextNum(A2)

 

[WednesdayC]

Hi Joe

The format will either be slashes or hyphens.
Yes, there will always be 3 characters followed by the number

Yes, I am open to a function, although I would prefer a formula, but if a function is the way to do it, then yes please.

Thanks, in advance, for any help you can give me.

Regards

Wednesday

 

[WednesdayC]

Hi Joe

It works brilliantly. Thank you so much.

Can it easily be adapted to work with a "/" or a "-" please.

Many thanks

Wednesday

 

[Joe4]

Here is an update of my previous code that will work regardless of whether they use slashes, dashes, or a combination of both:

Function NextNum(entry As String) As String

    Dim lastSlash As Long
    Dim lastDash As Long
    Dim last As Long
    Dim prefix As String
    Dim num As Long
    
'   Find last slash in entry
    lastSlash = InStrRev(entry, "/")
'   Find last dash in entry
    lastDash = InStrRev(entry, "-")
'   Take maximum of previous two values
    If lastDash > lastSlash Then
        last = lastDash
    Else
        last = lastSlash
    End If
    
'   Make sure there is an entry
    If (Len(entry) = 0) Or (last = 0) Then
        NextNum = ""
        Exit Function
    End If

'   Get prefix
    prefix = Left(entry, last + 3)
    
'   Get number to increment
    num = Right(entry, Len(entry) - (last + 3)) + 1
    
'   Build next number
    NextNum = prefix & num

End Function

 

[WednesdayC]

Hi Joe

Fantastic.

A huge thanks!

Regards

Wednesday

 

[Joe4]

You are welcome!

 

[jtakw]

Hi,

Here's a formula solution for what you described:

Book1
	A	B	C	D
1	3/1/2018/XXXX/XXX1	3/1/2018/XXXX/XXX16	3/1/2018/XXXX/XXX116	3-1-2018-XXXX-XXX116
2	3/1/2018/XXXX/XXX2	3/1/2018/XXXX/XXX17	3/1/2018/XXXX/XXX117	3-1-2018-XXXX-XXX117
3	3/1/2018/XXXX/XXX3	3/1/2018/XXXX/XXX18	3/1/2018/XXXX/XXX118	3-1-2018-XXXX-XXX118
4	3/1/2018/XXXX/XXX4	3/1/2018/XXXX/XXX19	3/1/2018/XXXX/XXX119	3-1-2018-XXXX-XXX119
5	3/1/2018/XXXX/XXX5	3/1/2018/XXXX/XXX20	3/1/2018/XXXX/XXX120	3-1-2018-XXXX-XXX120
6	3/1/2018/XXXX/XXX6	3/1/2018/XXXX/XXX21	3/1/2018/XXXX/XXX121	3-1-2018-XXXX-XXX121
7	3/1/2018/XXXX/XXX7	3/1/2018/XXXX/XXX22	3/1/2018/XXXX/XXX122	3-1-2018-XXXX-XXX122
8	3/1/2018/XXXX/XXX8	3/1/2018/XXXX/XXX23	3/1/2018/XXXX/XXX123	3-1-2018-XXXX-XXX123
9	3/1/2018/XXXX/XXX9	3/1/2018/XXXX/XXX24	3/1/2018/XXXX/XXX124	3-1-2018-XXXX-XXX124
10	3/1/2018/XXXX/XXX10	3/1/2018/XXXX/XXX25	3/1/2018/XXXX/XXX125	3-1-2018-XXXX-XXX125
Sheet181

Cell Formulas
Range	Formula
A2	=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1,"-","/"),"/","|",4))+3)&(RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1,"-","/"),"/","|",4))+3)))+1)

Formula copied down as far as needed.