Solved

Excel VBA

Posted on 2015-01-01
9
113 Views
Last Modified: 2015-01-04
How to write a Excel VBA code to break a field "18DFB0.23" such that

fld1 = 18
fld2 = DFB
fld3 = 0.23
0
Comment
Question by:AXISHK
9 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40527196
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40527197
Try this if the field will always be the same length.

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

Author Comment

by:AXISHK
ID: 40527216
Tks,  but the size of the each portion are difference, only the middle is a char field. Tks
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40527257
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
 

Author Comment

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

Author Closing Comment

by:AXISHK
ID: 40527295
Modification the code slightly and it works perfect, Tks
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40530816
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
 

Author Comment

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

Tks
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40530832
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question