VBA user defined function: Find position of last number in cell

[Showroom]

Hi

I have a cell (Let just say A1) which include both numbers and text. For example:

12x24Z

I want a user defined function, which give me the position of the last number in that cell. In this case the position for number 4 (position = 5).

I think I find a solution with the standard Excel functions, but it is a little heavy

So I thought I could so something smart by creating a user defined function in VBA, but so far I only have:



Function FindNum() as long

dim i as long

FindNum = 0



and nothing more. I am blank. Any ideas?

Thank you so much.

 

[lrobbo314]

How about this?

Function LASTPOS(s As String)
Dim b() As Byte:    b = s
For i = UBound(b) - 1 To 0 Step -2
    If b(i) > 47 And b(i) < 58 Then LASTPOS = (i / 2) + 1: Exit Function
Next i
End Function

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA User defined function to find position of last number in a cell

Hi I have a cell (Let just say A1) which include both numbers and text. For example: 12x24Z I want a user defined function, which give me the position of the last number in that cell. In this case the position for number 4 (position = 5). I think I find a solution with the standard...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[shinigamilight]

Function LastNumber(rng As Range)
        Dim Regex As Object
        Set Regex = CreateObject("VbScript.Regexp")
        Regex.Pattern = "[0-9]"
        Regex.Global = True
        Dim mc As MatchCollection
        Set mc = Regex.Execute(rng.Value)
       
        LastNumber = mc(mc.Count - 1).FirstIndex + 1
       
End Function

 

[Fluff]

If you have 365 how about a formula

=LEN(TEXTBEFORE(A1,SEQUENCE(10,,0),-1))+1