• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Excel VBA

How to write a Excel VBA code to break a field "18DFB0.23" such that

fld1 = 18
fld2 = DFB
fld3 = 0.23
0
AXISHK
Asked:
AXISHK
1 Solution
 
Wayne Taylor (webtubbs)Commented:
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)

Open in new window

0
 
Wilder1626Commented:
Try this if the field will always be the same length.

=LEFT(A1,2)
=MID(A1,3,3)
=RIGHT(A1,4)
0
 
AXISHKAuthor Commented:
Tks,  but the size of the each portion are difference, only the middle is a char field. Tks
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
MacroShadowCommented:
Put this code in a regular module (found it on Tek-Tips):
Public Function getPart(theString As String, intPartNumber As Integer) As String
    Dim aVarArray() As Variant
    aVarArray = SplitAlphaNumeric(theString)
    If Not intPartNumber > UBound(aVarArray()) + 1 Then
        getPart = aVarArray(intPartNumber - 1)
    End If
End Function
Public Function SplitAlphaNumeric(theString As String) As Variant
    Dim aVarArray() As Variant
    Dim intSplits As Integer
    Dim intIndex As Integer
    Dim intSwitchStart As Integer
    Dim intSwitchPos As Integer
    ReDim aVarArray(countParts(theString) - 1)
    intSwitchStart = 1
    Do
        intSplits = intSplits + 1
        intSwitchStart = intSwitchStart + intSwitchPos
        intSwitchPos = getPartLength(theString, intSwitchStart)
        aVarArray(intIndex) = Mid(theString, intSwitchStart, intSwitchPos)
        intIndex = intIndex + 1
    Loop Until intSwitchStart + intSwitchPos > Len(theString)
    SplitAlphaNumeric = aVarArray
End Function

Public Function countParts(theString As String) As Integer
    Dim isChrNumeric As Boolean
    Dim intcounter As Integer
    countParts = 1
    isChrNumeric = IsNumeric(Left(theString, 1))
    For intcounter = 2 To Len(theString)
        If Not (IsNumeric(Mid(theString, intcounter, 1)) = isChrNumeric) Then
            countParts = countParts + 1
            isChrNumeric = Not (isChrNumeric)
        End If
    Next intcounter
End Function

Public Function getPartLength(theString As String, intStart As Integer) As Integer
'Find the length of the alphabetic or numeric part
    Dim intCount As Integer
    intCount = 1
    If Not (IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + 1, 1))) Then
        intCount = 1
    Else
        Do
            intCount = intCount + 1
        Loop While IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + intCount, 1)) And Not ((intStart + intCount) > Len(theString))
    End If
    getPartLength = intCount
End Function

Open in new window

Then use it as follows:
fld1 = getPart("18DFB0.23",1)
fld2 = getPart("18DFB0.23",2)
fld3 = getPart("18DFB0.23",3)

Open in new window

0
 
AXISHKAuthor Commented:
fld3 = getPart("18DFB0.23",3) --> return 0 rather than 0.23... ?
0
 
AXISHKAuthor Commented:
Modification the code slightly and it works perfect, Tks
0
 
Wayne Taylor (webtubbs)Commented:
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

Open 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)

Open in new window

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

Tks
0
 
Wayne Taylor (webtubbs)Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now