Retrieving value after Last Decimal in String

[sshepherd10]

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.

input	output
1.1.1.2.3.1	001
1.1.1.2.3.2	002
1.1.1.2.3.3	003
1.1.1.2.3.4	004
1.1.1.2.3.5	005
1.1.1.2.3.6	006
1.1.1.2.3.7	007
1.1.1.2.3.8	008
1.1.1.2.3.9	009
1.1.1.2.3.10	010
1.1.1.2.3.11	011

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board
Would a UDF be ok

Function GetLastNumber(Cl As Range) As String
   GetLastNumber = format(Split(Cl, ".")(UBound(Split(Cl, "."))), "000")
End Function

Excel 2013/2016
	A	B
1	input	output
2	1.1.1.2.3.1	001
3	1.1.1.2.3.2	002
4	1.1.1.2.3.3	003
5	1.1.1.2.3.4	004
6	1.1.1.2.3.5	005
7	1.1.1.2.3.6	006
8	1.1.1.2.3.7	007
9	1.1.1.2.3.8	008
10	1.1.1.2.3.9	009
11	1.1.1.2.3.10	010
12	1.1.1.2.3.11	011
Output

Cell Formulas
Range	Formula
B2	=GetLastNumber(A2)

 

[Rick Rothstein]

Hi & welcome to the board
Would a UDF be ok

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

GetLastNumber = Format(Mid(Cl, InStrRev(Cl, ".") + 1), "000")

However, there is a formula solution available...

=TEXT(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99)),"000")

 

[sshepherd10]

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?

 

[sshepherd10]

Thank you guys very much! The engineering department is cheering!

 

[bosco_yip]

Another formula option :

=RIGHT(SUBSTITUTE(A2,".","00"),3)

Regards
Bosco

 

[shg]

That's very clever.

 

[Rick Rothstein]

That's very clever.

Agreed!

Edit Note
-------------------
The UDF code line code be changed to this instead...

GetLastNumber = Right(Replace(Cl, ".", "00"), 3)