Solved

# Excel VBA

Posted on 2015-01-01
102 Views
How to write a Excel VBA code to break a field "18DFB0.23" such that

fld1 = 18
fld2 = DFB
fld3 = 0.23
0
Question by:AXISHK

LVL 47

Expert Comment

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)
``````
0

LVL 11

Expert Comment

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

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

Author Comment

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

LVL 26

Accepted Solution

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
``````
Then use it as follows:
``````fld1 = getPart("18DFB0.23",1)
fld2 = getPart("18DFB0.23",2)
fld3 = getPart("18DFB0.23",3)
``````
0

Author Comment

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

Author Closing Comment

Modification the code slightly and it works perfect, Tks
0

LVL 47

Expert Comment

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
``````

....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)
``````
0

Author Comment

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

Tks
0

LVL 47

Expert Comment

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

### Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.