Left/Right/Mid

[prasadgov]

TIA,

How to get just the value, 50000000 from /FAMT/50000000,

 

[Gasman]

Use InstrRev() and Right()
Or you could use Split() but that would involve several lines of code.

 

[MajP]

mid(x, InStrRev(x, "/") + 1)

 

[plog]

We'd need more examples to make a valid general algorithm. Is it always after the 2nd slash? Could there be extra characters after it?

 

[The_Doc_Man]

If there is a format rule for that field such that the number you want is ALWAYS last, then MajP's suggestion is probably easiest and most direct. If there are variations to consider, we would need to know them all.

 

[prasadgov]

We'd need more examples to make a valid general algorithm. Is it always after the 2nd slash? Could there be extra characters after it?

No extra characters after that. That is how the data comes in always.

 

[gemma-the-husky]

Wouldn't Val return the correct value? Not sure if the slashes would interfere.

 

[The_Doc_Man]

Wouldn't Val return the correct value? Not sure if the slashes would interfere.

'Fraid not, Dave. "The Val function stops reading the string at the first character that it can't recognize as part of a number."

Val function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

I think that if it is always \xxxx\number then the InStrRev suggestion from MajP is best.

 

[MarkK]

If the prefix is always "/FAMT/" then use...

Mid([fieldname], 7)

 

[KitaYama]

You guys forgetting about the , at the end.

 

[arnelgp]

you can also use RegExp:

' chatgpt
Function ExtractNumbers(inputString As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    Dim matches As Object
    Dim match As Object
    Dim result As String
    
    ' Regular expression pattern to match numbers (including decimals)
    regex.Pattern = "\b\d+(\.\d+)?\b"
    regex.Global = True
    regex.IgnoreCase = True
    
    ' Check if there are matches
    If regex.Test(inputString) Then
        ' Get all matches
        Set matches = regex.Execute(inputString)
        
        ' Build the result string from matches
        For Each match In matches
            result = result & match.Value & " "
        Next match
        
        ' Remove trailing space
        result = Trim(result)
    End If
    
    ExtractNumbers = result
End Function

?ExtractNumbers("/FAMT/50000000,")
result: 50000000