CODEWITHSUNDEEP
excelvba
RawrRawr7
RawrRawr7

Reputation: 353

Trying to extract a 6 or 5 digit number from a string

I am trying to extract a 5 or 6 digit code from a string.

C:\Users\pthaxthon\Desktop\45697_Originals

C:\Users\pthaxthon\Desktop\123456_Originals

I just need the 5 or 6 digit number

The number always occur after the 4th dash and before the first _

I have tried using the mid and split command but with no success

Range("D14").Value = Mid(fle, 28, 6)

I just need the 5 or 6 digit number

Upvotes: 0

Views: 193

Answers (3)

DisplayName
DisplayName

Reputation: 13386

Since the number is always between the 28th digit and subsequent "_" character, you can go;

Range("D14").Value = Split(Mid(fle, 28), "_")(0)

Upvotes: 1

REXXman
REXXman

Reputation: 386

You could also use a regular expression to pull out the number.

    Set SDI = CreateObject("VBScript.RegExp")
    SDI.Pattern = "\d+"  '* keep the number only
    Set Num_out = SDI.Execute(Str_In)
    Criteria_out = Val(Num_out(0))

Upvotes: 1

Storax
Storax

Reputation: 12167

Another solution would be to rely on "The number always occur after the 4th dash and before the first _". Then you could use split in the following way

Option Explicit

Function GetNo(s As String) As String

    Const BSLASH = "\"
    Const UNDERSCORE = "_"
    Const FOUR = 4
    Dim v As Variant

    ' Split the string by backslash
    v = Split(s, BSLASH)
    ' Take always the fourth entry and split it by underscore
    v = Split(v(FOUR), UNDERSCORE)
    ' take string before the underscore
    GetNo = v(0)

End Function

Sub TestIt()
Dim inp As String
    inp = "C:\Users\pthaxthon\Desktop\123456_Originals"
    inp = "C:\Users\pthaxthon\Desktop\45697_Originals"
    Debug.Print GetNo(inp)
End Sub

Upvotes: 1

Related Questions