Retrieving value after Last Decimal in String

sshepherd10

New Member
Joined
Jul 13, 2018
Messages
3
Hi all, I am frustrated. My Engineers and I manually convert strings of numbers into sequence numbers for our ERP system. It is waste of time and brings in the chance for errors.

SolidWorks (CAD) spits out a number = 1.2.3.4.10 and we change that to Sequence Number = 010

I really have a tough time getting anything above 009 to work, That is why I wanted to get the 010 example to work

Essentially we are taking the numbers after the last decimal and converting them to a three digit number from 001-999.

So that is the problem. I won't say how I have tried to accomplish this, as you will find a much better solution than I.


[TABLE="width: 219"]
<tbody>[TR]
[TD]input[/TD]
[TD]output[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.1[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.2[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.3[/TD]
[TD]003[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.4[/TD]
[TD]004[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.5[/TD]
[TD]005[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.6[/TD]
[TD]006[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.7[/TD]
[TD]007[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.8[/TD]
[TD]008[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.9[/TD]
[TD]009[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.10[/TD]
[TD]010[/TD]
[/TR]
[TR]
[TD]1.1.1.2.3.11[/TD]
[TD]011[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi & welcome to the board
Would a UDF be ok
Code:
Function GetLastNumber(Cl As Range) As String
   GetLastNumber = format(Split(Cl, ".")(UBound(Split(Cl, "."))), "000")
End Function
Excel 2013/2016
AB
1inputoutput
21.1.1.2.3.1001
31.1.1.2.3.2002
41.1.1.2.3.3003
51.1.1.2.3.4004
61.1.1.2.3.5005
71.1.1.2.3.6006
81.1.1.2.3.7007
91.1.1.2.3.8008
101.1.1.2.3.9009
111.1.1.2.3.10010
121.1.1.2.3.11011
Output
Cell Formulas
RangeFormula
B2=GetLastNumber(A2)
 
Upvote 0
Hi & welcome to the board
Would a UDF be ok
Code:
Function GetLastNumber(Cl As Range) As String
   [B][COLOR="#FF0000"]GetLastNumber = format(Split(Cl, ".")(UBound(Split(Cl, "."))), "000")[/COLOR][/B]
End Function
This might be a smidgeon (read that as unnoticeably) faster...
Code:
GetLastNumber = Format(Mid(Cl, InStrRev(Cl, ".") + 1), "000")

However, there is a formula solution available...

=TEXT(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99)),"000")
 
Last edited:
Upvote 0
Thank you for the quick response and kind words. I am trying to get this into a VBA Macro. I saved the workbook as .xlsm but when I go to the developer>Visual basic I seem to not be able to great a new module. Any thoughts?
 
Upvote 0
That's very clever.
Agreed!

Edit Note
-------------------
The UDF code line code be changed to this instead...
Code:
GetLastNumber = Right(Replace(Cl, ".", "00"), 3)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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