Excel VBA

Will each field always be the same length? If so, you can use something like this....

    Dim s As String
    s = "18DFB0.23"
    
    Dim fld1 As Integer, fld2 As String, fld3 As Double
    
    fld1 = CInt(Left(s, 2))
    fld2 = Mid(s, 3, 3)
    fld3 = Right(s, 4)

Select allOpen in new window

Try this if the field will always be the same length.

=LEFT(A1,2)
=MID(A1,3,3)
=RIGHT(A1,4)

Tks,  but the size of the each portion are difference, only the middle is a char field. Tks

fld3 = getPart("18DFB0.23",3) --> return 0 rather than 0.23... ?

Modification the code slightly and it works perfect, Tks

While this question has already been answered, another option is to use Regular Expressions. While somewhat difficult to understand, they can make your job much easier.

Use this function....

Function GetParts(inputString As String) As Variant

    Dim regex, matches
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\d+)([A-Z]+)(\d+.\d+)"
    If regex.Test(inputString) Then
        Set matches = regex.Execute(inputString)
        Dim arr(0 To 2) As Variant
        arr(0) = matches.Item(0).SubMatches(0)
        arr(1) = matches.Item(0).SubMatches(1)
        arr(2) = matches.Item(0).SubMatches(2)
        GetParts = arr
    End If
    
End Function

Select allOpen in new window

....like this....

    Dim fld1 As Integer, fld2 As String, fld3 As Double
    Dim arr() As Variant
    arr = GetParts("18DFB0.23")
    fld1 = arr(0)
    fld2 = arr(1)
    fld3 = arr(2)

Select allOpen in new window

What does regex.Pattern = "(\d+)([A-Z]+)(\d+.\d+)" in the expression mean ?

Tks

It's matching a pattern of 3 groups....

(\d+) = match a numeral character that occurs one or more times
([A-Z]+) = Match any character from A to Z that occurs 1 or more times
(\d+.\d+) = Match a one or more numerals, followed by a decimal place, followed by one or more numerals