<div>
Is there only one numeric piece 
eg junk123garbage 
 
or more than one numeric piece 
eg 14junk123garbage21 
 
And, do you do this with 100's 1000's or millions of rows, or just one piece of data. 
A nasty-slow algorithm for one piece doesn't matter for a few data points. 
For millions, you need efficiency
</div>

<div>
place this codes in a regular module 
 

<pre><code class="code-10 nolinks" id="code-20-41448059-1">Function GetNumbers(strIn As String)
 Dim j, numOnly
 If strIn = &quot;&quot; Then Exit Function
 For j = 1 To Len(strIn)
 If IsNumeric(Mid(strIn, j, 1)) Then
 numOnly = numOnly &amp; Mid(strIn, j, 1)
 End If
 Next
 GetNumbers = numOnly
 End Function
</code></pre>
<pre><code class="code-1 nolinks" id="code-20-41448059-2">Sub whatever()
Dim EPW As String, NPU As String, vNum
EPW = &quot;%X123cy!&quot;
vNum = GetNumbers(EPW)
NPU = Replace(EPW, vNum, vNum + 1)
Debug.Print NPU
End Sub
</code></pre>
 
 
run the sub whatever
</div>

<div>
that is assuming the numbers appears consecutively
</div>

<div>
Thank you for the quick responses. 
 
I am only doing this for a single data point, but I do need it to be quick. 
 
The numbers can be anywhere, but I only need to update the last &quot;set&quot; of numbers. &nbsp;If the variable is 23!xTr93%, I only need to update the 93 to 94.
</div>

<div>
are there any other gotcha... how many possible SETS of number can be in the string?
</div>

<div>
Honestly, I don't know how many sets of numbers could be in the string, there is no set limit. &nbsp; 
 
However, with the max characters in the field being 50, realistically, there could be 25 different sets of numbers. &nbsp; 
 
I only care about the last set of numbers in the string.
</div>

<div>
Rey, I tested the code you sent and it is working as you deigned based on a single set of numbers in the string, unless there are zeros at the beginning of the number. &nbsp; 
 
For example, ST0040; the code returns ST41 instead of ST0041.
</div>

<div>
ok test this revised code 
 

<pre><code class="code-10 nolinks" id="code-20-41448172-1">Sub whatever()
Dim EPW As String, NPU As String, vNum As String, vNum2 As String
EPW = &quot;ST0040&quot;
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1
If Left(vNum, 1) = 0 Then
 Select Case Len(Trim(vNum))
 Case 2
 vNum2 = Format(vNum2, &quot;00&quot;)
 Case 3
 vNum2 = Format(vNum2, &quot;000&quot;)
 Case 4
 vNum2 = Format(vNum2, &quot;0000&quot;)
 Case 5
 vNum2 = Format(vNum2, &quot;00000&quot;)
 
 End Select
 
End If

NPU = Replace(EPW, vNum, vNum2)
Debug.Print NPU
End Sub
</code></pre>
</div>

<div>
Ah, that's a little ugly give we could have 50 characters 
Compare some lengths and we can pad out without assumptions 
 

<pre><code class="code-10 nolinks" id="code-20-41448189-1">Private Sub whatever

Dim EPW As String, NPU As String, vNum As String, vNum2 As String, x As Integer
EPW = &quot;ST0000000040&quot;

'vnum is a string
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1

If Len(vNum2) &lt;&gt; Len(vNum) Then 'some zeros disappeared in the math
 For x = Len(vNum2) To Len(vNum) - 1
 vNum2 = &quot;0&quot; &amp; vNum2
 Next x
End If

NPU = Replace(EPW, vNum, vNum2)
Debug.Print NPU
End Sub
</code></pre>
 
9, 99, 999, 9999 etc remain problems. 
Let me think about that, too.
</div>

<div>
Thank you for all of your help. 
 
Realistically, I doubt that any of the values will ever be more than 25 characters in length.
</div>

<div>
No problem. 
&quot;!12bah45guh0000009999blah*&quot; returns 
&quot;!12bah45guh0000010000blah*&quot; so that should do it 
&lt;grin&gt;
</div>

<div>
@Nick 
nice copying of codes...
</div>

<div>
No need to re-invent the wheel, just tweak it. 
A Select Case with theoretically 49 zeros to pad was a bit clunky. 
The rest was ok. 
I wouldn't do one line Dim'ming or Debug.Print if it were me, but there's not much point in confusing the issue over semantics and style. 
 
So I didn't. I tweaked the part that needed improving.
</div>

<div>
next time come up with YOUR original codes. 
and refrain commenting about the codes of others.
</div>

<div>
There's no decimals or commas likely to happen, is there? 
Because we're doing math for the incrementing here and 
ST00.99 and ST100,999 will be problems if that can happen. 
 
If not, then @rsburge I think you have what you need. 
Give @Rey the points if you want. 
Good solutions matter to me, not points. 
 
Nick67
</div>

<div>
@Nick, 
the point is, you should not be hijacking codes.
</div>

<div>
Here is a regular expression approach: 

<pre><code class="code-10 nolinks" id="code-20-41448250-1">Function Q_28923566(parmString As String) As String
 Static oRE As Object
 Dim oMatches As Object
 Dim oM As Object
 Dim strLastMatch As String
 Dim vValue As Variant
 
 If oRE Is Nothing Then
 Set oRE = CreateObject(&quot;vbscript.regexp&quot;)
 oRE.Global = True
 oRE.Pattern = &quot;\d+&quot;
 End If

 If oRE.test(parmString) Then
 Set oMatches = oRE.Execute(parmString)
 Set oM = oMatches(oMatches.Count - 1)
 On Error Resume Next
 strLastMatch = oM.Value
 Err.Clear
 vValue = CDec(strLastMatch) + 1
 If Err = 0 Then
 Q_28923566 = Left(parmString, oM.firstindex) &amp; Replace(parmString, strLastMatch, Format(vValue, String(Len(strLastMatch), &quot;0&quot;)), Start:=oM.firstindex + 1)
 Else
 Q_28923566 = parmString
 Error 6
 End If
 End If

End Function
</code></pre>
***edited
</div>

<div>
Note: Since I am using a Decimal data type, there is a limit to the number of consecutive non-zero digits that can be processed with my regex solution.
</div>

<div>
The point is if we spent time creating two code bases that do EXACTLY the same thing but look different for fun and giggles that's both a waste of our time and the Asker's. 
And pointless. 
 
Code reuse and iteration is pretty much the lifeblood of programming. 
And iterative solution tweaking is a big part of what makes EE discussions interesting to follow. 
Your code is not hijacked. 
Everyone can see what your contribution was. 
No one discounts it. 
 
Do you like this better? 
Working sample attached 
 

<pre><code class="code-10 nolinks" id="code-20-41448255-1">Private Sub IterateString()

Dim EPW As String
Dim NPU As String
Dim InitialNumberString As String
Dim FinalnumberString As String
Dim x As Integer
EPW = Me.EPW

InitialNumberString = StrReverse(ExtractLastNumbers(EPW))
FinalnumberString = InitialNumberString + 1

If Len(FinalnumberString) &lt; Len(InitialNumberString) Then 'some zeros disappeared in the math
 For x = Len(FinalnumberString) To Len(InitialNumberString) - 1
 FinalnumberString = &quot;0&quot; &amp; FinalnumberString
 Next x
End If

Me.NPU = Replace(EPW, InitialNumberString, FinalnumberString)

End Sub

Private Function ExtractLastNumbers(EPW As String)
Dim x As Long
Dim theFinalNumberString As String
Dim GotTheNumber As Boolean
If Nz(EPW, &quot;&quot;) = &quot;&quot; Then Exit Function
For x = Len(EPW) To 1 Step -1
 If IsNumeric(Mid(EPW, x, 1)) Then
 GotTheNumber = True
 theFinalNumberString = theFinalNumberString &amp; Mid(EPW, x, 1)
 Else
 If GotTheNumber = True Then Exit For
 End If
Next
ExtractLastNumbers = theFinalNumberString
End Function
</code></pre>
 
Same functionality. 
Is there a point to that? 
Still doesn't test for commas decimals or European commas-as-decimal type issues. 
<a href="https://filedb.experts-exchange.com/incoming/2016/02_w06/1079509/incrementer.mdb" target="_blank" class="file-inline" title="working sample (144 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>incrementer.mdb</a>
</div>

incrementer.mdb

<div>
Thank you all for your help. 
 
It is unlikely that there will be decimals or commas, but it is not impossible. &nbsp;Do you think this is something I should worry about? 
 
The combination of the code provided by Rey and the tweaks provided by Nick67 are working to do what I need. 
 
I will also try the other two functions that were provided before I accept solutions. 
 
As a side note, Experts Exchange is an invaluable resource that I have been using for many years. &nbsp;I agree with the last post from @Nick67. &nbsp;I almost always receive assistance from multiple experts, and the end solution is often a combination of the solutions provided. &nbsp;I did not feel that Nick67 hijacked any code, rather Nick67 was just trying to make it a bit more efficient. &nbsp;Personally, I appreciate this type of input; it helps me to learn more and understand reasons for the different code designs. 
 
Generally speaking, I believe that the Asker will take into consideration all contributions to the solution when awarding points... &nbsp;I always do.
</div>

<div>
here is the revised codes 
 

<pre><code class="code-10 nolinks" id="code-20-41448278-1">Sub whatever()
Dim EPW As String, NPU As String, vNum As String, vNum2 As String
EPW = &quot;ST0040&quot;
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1
vNum = Val(vNum)

NPU = Replace(EPW, vNum, vNum2)
Debug.Print NPU
End Sub
</code></pre>
</div>

<div>
@rsburge 
 
With a max length of 50 characters, what is the largest (length) consecutive digit sequence?
</div>

<div>
see which is more efficient, the one that iterates or this codes 
 

<pre><code class="code-10 nolinks" id="code-20-41448329-1">Sub whatever()
Dim EPW As String, NPU As String, vNum As String, vNum2 As String, xNum
EPW = &quot;ST000999&quot;
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1
xNum = Val(vNum)
If Len(xNum) &lt;&gt; Len(vNum) Then 'there are leading zero
 If Len(vNum2) &gt; Len(xNum) Then 
 NPU = Replace(EPW, &quot;0&quot; &amp; xNum, vNum2)
 End If
Else
 NPU = Replace(EPW, xNum, vNum2)
End If
Debug.Print NPU
End Sub
</code></pre>
<pre><code class="code-10 nolinks" id="code-20-41448329-2">Function GetNumbers(strIn As String)
 Dim j, numOnly, blnFoundFirstSet As Boolean, blnFoundLastSet As Boolean
 If strIn = &quot;&quot; Then Exit Function
 For j = Len(strIn) To 1 Step -1
 If IsNumeric(Mid(strIn, j, 1)) Then
 blnFoundFirstSet = True
 numOnly = numOnly &amp; Mid(strIn, j, 1)
 Else
 If blnFoundFirstSet Then Exit For
 End If
 Next
 GetNumbers = numOnly
 End Function
</code></pre>
</div>

<div>
@aikimark The longest length numeric sequence would be 48 digits. 
 
However, I have sent a note to the powers that be letting them know I feel 20 characters is sufficient for their purpose, and to limit the number of digits to 10; this would mean the largest number looking at consecutive digits would be 9,999,999,999. 
 
I hope that makes sense.
</div>

<div>
@Rey 
Your new function falls over with the following string, returning nothing 
EPW = &quot;!12bah45guh000000999900blah*&quot; 
 
@rsburge 
Regex is powerful and a useful thing to have in your toolbox 
I don't. 
But there's a great article here 
<a href="https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html" rel="ugc">https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html</a> 
 
I'm still think about decimals and commas. 
Mine turned !12bah45guh0000009999.00blah* into 
!12bah45guh0101019999.01blah*
</div>

<div>
easy 
 

<pre><code class="code-10 nolinks" id="code-20-41449255-1">Sub whatever()
Dim EPW As String, NPU As String, vNum As String, vNum2 As String, xNum
EPW = &quot;!12bah45guh000000999900blah*&quot;
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1
xNum = Val(vNum)
If Len(xNum) &lt;&gt; Len(vNum) Then 'there are leading zero
 If Len(vNum2) &gt; Len(xNum) Then
 NPU = Replace(EPW, &quot;0&quot; &amp; xNum, vNum2)
 ElseIf Len(vNum2) = Len(xNum) Then
 NPU = Replace(EPW, xNum, vNum2)
 End If
Else
 NPU = Replace(EPW, xNum, vNum2)
End If
Debug.Print NPU
End Sub
</code></pre>
</div>

<div>
Thank you for all of the help, everyone. 
 
Right now I have the GetNumbers code from @Rey with the Whatever code tweaked by @Nick67 working as desired. &nbsp;These together are the most efficient in my project. &nbsp;I did comment out the msgbox after testing. 
 
I think I have the powers that be convinced to stick with 20 characters and to disallow commas and periods. &nbsp;Should that change, and I need more help, I will open up a related question. 
 
@Nick67, than for the info on regular expressions. &nbsp;They confuse the heck out of me, but hopefully, that article will shed some light. 
 
Thank you again everyone for your help. &nbsp;It is truly appreciated. 
 

<pre><code class="code-10 nolinks" id="code-20-41449274-1">Function GetNumbers(strIn As String)
 Dim j, numOnly, blnFoundFirstSet As Boolean, blnFoundLastSet As Boolean
 If strIn = &quot;&quot; Then Exit Function
 For j = Len(strIn) To 1 Step -1
 If IsNumeric(Mid(strIn, j, 1)) Then
 blnFoundFirstSet = True
 numOnly = numOnly &amp; Mid(strIn, j, 1)
 Else
 If blnFoundFirstSet Then Exit For
 End If
 Next
 GetNumbers = numOnly
 End Function
</code></pre>
 

<pre><code class="code-10 nolinks" id="code-20-41449274-2">Private Sub whatever()

Dim EPW As String, NPU As String, vNum As String, vNum2 As String, x As Integer
EPW = &quot;ST099&quot;

'vnum is a string
vNum = StrReverse(GetNumbers(EPW))
vNum2 = vNum + 1

If Len(vNum2) &lt; Len(vNum) Then 'some zeros disappeared in the math
 For x = Len(vNum2) To Len(vNum) - 1
 vNum2 = &quot;0&quot; &amp; vNum2
 Next x
End If

NPU = Replace(EPW, vNum, vNum2)
MsgBox NPU
End Sub
</code></pre>
</div>

<div>
Good enough! 
and to disallow commas and periods. 
Good! because that starts getting ugly quick 
99,999,999.99 is a valid number vs 999,999,99.99 which is not. 
Those kinds of things get hard to parse. 
 
Throw in that this is a valid European number 99.999.999,99 and now things start getting off the chain. 
Glad you got it cased. 
 
Nick67
</div>

<div>
<div class="content wysiwyg-content">
Hello Experts - 
 
In Access 2007, I have a variable called EPW. &nbsp;The value of the variable could be any length up to 50 characters, and any combination of letters, numbers and special characters; for example, %X123cy! or $Hsp1@, etc. 
 
I need to extract the numeric portion, increase it by 1 and then put it all back together in another variable called NPU. 
 
Steps... 
1. EPW Value = %X123cy! 
2. Extract 123 
3. Increase to 124 
4. Combine so NPU = %X124cy! 
 
I am a novice with VBA, so the more code example you can provide, the better. 
 
Thank you!
</div>
</div>

Hello Experts - 

In Access 2007, I have a variable called EPW.  The value of the variable could be any length up to 50 characters, and any combination of letters, numbers and special characters; for example, %X123cy! or $Hsp1@, etc.

I need to extract the numeric portion, increase it by 1 and then put it all back together in another variable called NPU.

Steps...
1. EPW Value = %X123cy!
2. Extract 123
3. Increase to 124
4. Combine so NPU = %X124cy!

I am a novice with VBA, so the more code example you can provide, the better.

Thank you!

Extract Number from Variable, Increase by 1 and Add Back to Variable

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Programming includes both the specifics of the language you’re using, like Visual Basic, .NET, Java and others, but also the best practices in user experience and interfaces and the management of projects, version control and development. Other programming topics are related to web and cloud development and system and hardware programming.

Programming

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.