Solved

split function

Posted on 2008-10-13
8
258 Views
Last Modified: 2012-05-05
I would like to use a split function into my reader
    I like to take B1G1 and replace the first number 1 after the B to a value of 1 and the 1 after the G into
a 1 value these will be 2 separate values. Please how can this be done in vb
0
Comment
Question by:Seven price
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 300 total points
ID: 22703179
Please define your original data better, maybe provide a few more examples.  Also, let us know how the data needs to end up.

Split doesn't sound like a good candidate; it chops a string up based on a recurring character.  It works great for comma-delimited strings but not with a variable delimiter.
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 100 total points
ID: 22703223
This is not a use of the split function.  You cannot define a 'split character' by which to break the string into 'pieces'
 
The Split function is used to break a string composed of identifiable 'delimited' parts into those parts - such as a comma delimited string:  "A,B,C,D"  where the , is the delimiter, and the Split function would return the set of values "A", "B"< "C" and "D" as separate strings.
You have A1B1, and you want to change the 1 after the A to one value, and the 1 after the B to a different value.  
 
Is your string explicitly A1B1, or does A represent a more complex set of characters?
 
AW
0
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 100 total points
ID: 22703262
Probably a String.Replace() method will fit for you:
http://msdn.microsoft.com/en-us/library/fk49wtc1.aspx 
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 9

Author Comment

by:Seven price
ID: 22703302
To Arthur_Wood:
I want to remove the B and the G and just have a value for 1
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 300 total points
ID: 22703522
How about some more examples of your data, obviously it's not always B1G1?
0
 
LVL 9

Author Comment

by:Seven price
ID: 22703635
Its a lot of code. If you like I will post the whole thing. But for now lets see if we can start here.
I am reading the columns in an excel sheet. for example B1G1 is in row 5.

This is what i did for row 4.




 Private Sub convertMeasure(ByVal i As Integer)
 
        Dim x As Integer
        For x = 0 To i
            a = exData(x, 4)
            If exData(x, 4) = "Oz" Then
                exData(x, 4) = 0
            ElseIf exData(x, 4) = "z" Then
                exData(x, 4) = 0
            ElseIf exData(x, 4) = "Z" Then
                exData(x, 4) = 0
            ElseIf exData(x, 4) = "Fl Oz" Then
                exData(x, 4) = 1
            ElseIf exData(x, 4) = "QT" Then
                exData(x, 4) = 2
            ElseIf exData(x, 4) = "Quart" Then
                exData(x, 4) = 2
            ElseIf exData(x, 4) = "LT" Then
                exData(x, 4) = 3
            ElseIf exData(x, 4) = "Ltr" Then
                exData(x, 4) = 3
            ElseIf exData(x, 4) = "PT" Then
                exData(x, 4) = 4
            ElseIf exData(x, 4) = "Pint" Then
                exData(x, 4) = 4
            ElseIf exData(x, 4) = "GAL" Then
                exData(x, 4) = 5
            ElseIf exData(x, 4) = "Gallon" Then
                exData(x, 4) = 5
            ElseIf exData(x, 4) = "Lb" Then
                exData(x, 4) = 6
            ElseIf exData(x, 4) = "S" Then
                exData(x, 4) = 7
            ElseIf exData(x, 4) = "FT" Then
                exData(x, 4) = 8
            ElseIf exData(x, 4) = "SQ FT" Then
                exData(x, 4) = 9
            ElseIf exData(x, 4) = "Sq Ft" Then
                exData(x, 4) = 9
            ElseIf exData(x, 4) = "1000S" Then
                exData(x, 4) = 10
            ElseIf exData(x, 4) = "10SF" Then
                exData(x, 4) = 11
            ElseIf exData(x, 4) = "100F" Then
                exData(x, 4) = 12
            ElseIf exData(x, 4) = "Dzn" Then
                exData(x, 4) = 15
            ElseIf exData(x, 4) = "Dz" Then
                exData(x, 4) = 15
            End If
        Next x
 
    End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Seven price
ID: 22703676
here is the whole code

Public Sub loadDT()
        '"Data Source=" & Server.MapPath("/store/temp/041001/Grocery.xls") & ";" & _
        
        Dim DS As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim sqlStr As String = "SELECT [UPC], DESCRIPTION, PK, [SIZE], SALE,[CASE COST],DEAL,[DEAL COST],[UNIT COST],RETAIL FROM [Batch$]", extendedString As String
 
        'C:\Excel\010120080711.XLS; " & _
        MyConnection = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & Server.MapPath("\store\temp\" & StoreID & "\" & FileInfo) & "; " & _
              "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";")
 
        Dim command As New OleDbCommand(sqlStr, MyConnection)
        MyConnection.Open()
 
        myReader = command.ExecuteReader()
        '*************************Code begins here****************************************************
        '*************************Code begins here****************************************************
        Dim i As Integer, j As Integer, makeChange As Boolean, randNum As Integer = 123456
        i = 0 : j = 0
        While myReader.Read() 'Populate exData array with DataReader
 
            Dim l As Integer = Len(myReader(0).ToString())  'Get length of the item in reader
            '***********************READ IN KRASDALE CODE AND UPC**********************
            If l = 6 Then
                fromKCtoUPC(kcIndex) = myReader(0) 'Read in Krasdale Code
                kcIndex = kcIndex + 1
            ElseIf l > 6 Then
                fromKCtoUPC(kcIndex) = myReader(0) 'Read in UPC
                kcIndex = kcIndex + 1
            End If
            '******************COLLECT DATA FOR KRASDALE CODE LENGTH = 6******************************
            If l = 6 Then
                For j = 0 To 12
                    exData(i, 0) = myReader(0).ToString()
                    exData(i, 1) = myReader(1).ToString()
                    exData(i, 2) = myReader(2).ToString()
                    exData(i, 3) = myReader(3).ToString()
                    exData(i, 4) = ""
                    exData(i, 5) = myReader(4).ToString()
                    exData(i, 6) = ""
                    exData(i, 7) = myReader(5).ToString()
                    exData(i, 8) = myReader(6).ToString()
                    exData(i, 9) = myReader(7).ToString()
                    exData(i, 10) = myReader(8).ToString()
                    exData(i, 11) = ""
                    exData(i, 12) = myReader(9).ToString()
 
                    If j >= 12 Then
                        i = i + 1
                    End If
                Next j
            Else
                Try
                    toUPC(i - 1) = myReader(0) 'Collect UPC
                Catch
                End Try
            End If
 
        End While
        '***********************************************************************************************
        myReader.Close() 'Close connection string
        MyConnection.Close()
 
        '*****************************Add Rows to DataTable**********************************
 
        'Fill in missing areas in the array
        Call fillArrays1(i)
        Call BubbleSort(exData, i - 1)
        Call convertMeasure(i)
        Call convertError(i)
        ' Call ConvertBig(i)
        '**********************************************
        '***********************************************************************************************
 
 
 
 
 
        Dim rowCt As Integer, row1 As DataRow
        row1 = exDataTable.NewRow
 
 
        For rowCt = 0 To i
 
            Dim newLn As Integer = Len(exData(rowCt, 0))
            Dim fstLtr As String = Left(exData(rowCt, 0), 1)
 
            If newLn = 11 And fstLtr = "2" Then
 
            Else
                row1 = exDataTable.NewRow()
                row1("UPC") = exData(rowCt, 0)
                row1("Description") = exData(rowCt, 1)
                row1("PK") = exData(rowCt, 2)
                row1("SizeUnit") = exData(rowCt, 3)
                row1("SizeMeasureID") = exData(rowCt, 4)
                row1("SLQty") = exData(rowCt, 5)
                row1("SLPrice") = exData(rowCt, 6)
                row1("CASE COST") = exData(rowCt, 7)
                row1("DEAL") = exData(rowCt, 8)
                row1("DEAL COST") = exData(rowCt, 9)
                row1("UNIT QTY") = exData(rowCt, 10)
                row1("UNIT COST") = exData(rowCt, 11)
                row1("RETAIL") = exData(rowCt, 12)
 
                ' Be sure to add the new row to the DataRowCollection. 
                exDataTable.Rows.Add(row1)
            End If
        Next rowCt
        DataGrid7.DataSource = exDataTable
        DataGrid7.DataBind()
    End Sub
 
Private Sub fillArrays1(ByVal i As Integer)
        '*************************Array is Loaded with data form 100120080404***************************
        'Begin fill
        '***********************************************************************************************
        'Check if these indices are populated
        Dim q As Integer, r As Integer, y As Integer
        Dim start As Integer = 0, start1 As Integer = 0
        For q = 0 To i
 
            '*******************************Condition 1*****************************************************
            If exData(q, 3) <> "" And exData(q, 4) = "" And exData(q, 5) <> "" And exData(q, 6) = "" _
            And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" And exData(q, 10) <> "" _
            And exData(q, 11) = "" And exData(q, 12) <> "" Then
 
                Dim tony As Integer = get3Value(start1, q)
                Dim id As Integer
 
                For y = start To q
 
                    'Check array filled by reader
                    If exData(y, 2) = "" And exData(y, 3) = "" And exData(y, 4) = "" And exData(y, 5) = "" _
                    And exData(y, 6) = "" And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _
                    And exData(y, 10) = "" And exData(y, 11) = "" And exData(y, 12) = "" Then
 
                        '1. All is empty
                        If y < endPTSIndex(0) Then
                            a = SplitFrontSZ(endPTSValue(0))
                            b = SplitBackSZ(endPTSValue(0))
                        ElseIf y > endPTSIndex(0) And y <= endPTSIndex(1) Then
                            a = SplitFrontSZ(endPTSValue(1))
                            b = SplitBackSZ(endPTSValue(1))
                        ElseIf y > endPTSIndex(1) And y <= endPTSIndex(2) Then
                            a = SplitFrontSZ(endPTSValue(2))
                            b = SplitBackSZ(endPTSValue(2))
                        ElseIf y > endPTSIndex(2) And y <= endPTSIndex(3) Then
                            a = SplitFrontSZ(endPTSValue(3))
                            b = SplitBackSZ(endPTSValue(3))
                        ElseIf y > endPTSIndex(3) And y <= endPTSIndex(4) Then
                            a = SplitFrontSZ(endPTSValue(4))
                            b = SplitBackSZ(endPTSValue(4))
                        ElseIf y > endPTSIndex(4) And y <= endPTSIndex(5) Then
                            a = SplitFrontSZ(endPTSValue(5))
                            b = SplitBackSZ(endPTSValue(5))
                        ElseIf y > endPTSIndex(5) And y <= endPTSIndex(6) Then
                            a = SplitFrontSZ(endPTSValue(6))
                            b = SplitBackSZ(endPTSValue(6))
                        ElseIf y > endPTSIndex(6) And y <= endPTSIndex(7) Then
                            a = SplitFrontSZ(endPTSValue(7))
                            b = SplitBackSZ(endPTSValue(7))
                        ElseIf y > endPTSIndex(7) And y <= endPTSIndex(8) Then
                            a = SplitFrontSZ(endPTSValue(8))
                            b = SplitBackSZ(endPTSValue(8))
                        ElseIf y > endPTSIndex(8) And y <= endPTSIndex(9) Then
                            a = SplitFrontSZ(endPTSValue(9))
                            b = SplitBackSZ(endPTSValue(9))
                        ElseIf y > endPTSIndex(9) And y <= endPTSIndex(10) Then
                            a = SplitFrontSZ(endPTSValue(10))
                            b = SplitBackSZ(endPTSValue(10))
                        Else
                            a = SplitFrontSZ(exData(q, 3))
                            b = SplitBackSZ(exData(q, 3))
                        End If
 
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 7) = exData(q, 7)
                        exData(y, 8) = exData(q, 8)
                        exData(y, 9) = exData(q, 9)
                        exData(y, 10) = e
                        exData(y, 11) = f
                        exData(y, 12) = exData(q, 12)
 
                    ElseIf (exData(y, 2) <> "" Or exData(y, 2) = "") And (exData(y, 3) = "" Or exData(y, 3) <> "") _
                    And exData(y, 4) = "" And exData(y, 5) = "" And exData(y, 6) = "" And exData(y, 7) <> "" _
                    And exData(y, 8) <> "" And exData(y, 9) <> "" And exData(y, 10) <> "" And exData(y, 11) = "" _
                    And (exData(y, 12) = "" Or exData(y, 12) <> "") Then
                        '2. 2 if full or empty ,3,4,5,6,11 is empty, 12 is full or is empty
 
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        If exData(y, 3) = "" Then
                            a = SplitFrontSZ(exData(q, 3))
                            b = SplitBackSZ(exData(q, 3))
                            exData(y, 3) = a
                        Else
                            a = SplitFrontSZ(exData(y, 3))
                            b = SplitBackSZ(exData(y, 3))
                            exData(y, 3) = a
                        End If
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 10) = e
                        exData(y, 11) = f
                        If exData(y, 12) = "" Then
                            exData(y, 12) = exData(q, 12)
                        End If
 
                    ElseIf exData(y, 2) = "" And exData(y, 3) <> "" And exData(y, 4) = "" And exData(y, 5) = "" _
                    And exData(y, 6) = "" And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _
                    And exData(y, 10) = "" And (exData(y, 11) = "" Or exData(y, 11) <> "") Then
                        '3. 3 is NOT empty
                        a = SplitFrontSZ(exData(y, 3))
                        b = SplitBackSZ(exData(y, 3))
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 7) = exData(q, 7)
                        exData(y, 8) = exData(q, 8)
                        exData(y, 9) = exData(q, 9)
                        exData(y, 10) = e
                        exData(y, 11) = f
                        If exData(y, 12) = "" Then
                            exData(y, 12) = exData(q, 12)
                        End If
 
                    ElseIf exData(y, 2) <> "" And exData(y, 3) <> "" And exData(y, 4) <> "" And exData(y, 5) = "" _
                    And exData(y, 6) = "" And exData(y, 7) <> "" And exData(y, 8) <> "" And exData(y, 9) <> "" _
                    And exData(y, 10) <> "" And exData(y, 11) <> "" And exData(y, 12) = "" Then
 
                        '4. 5, 6, 12 is empty
                        a = SplitFrontSZ(exData(q, 3))
                        b = SplitBackSZ(exData(q, 3))
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 12) = exData(q, 12)
 
                    ElseIf exData(y, 2) <> "" And exData(y, 3) <> "" And exData(y, 4) = "" And exData(y, 5) <> "" _
                    And exData(y, 6) = "" And exData(y, 7) <> "" And exData(y, 8) <> "" And exData(y, 9) <> "" _
                    And exData(y, 10) <> "" And exData(y, 11) = "" And exData(y, 12) <> "" Then
 
                        '5. 4, 6, 11 is empty
                        'Get values to split in two.
                        a = SplitFrontSZ(exData(q, 3))
                        b = SplitBackSZ(exData(q, 3))
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 10) = e
                        exData(y, 11) = f
 
                    End If
                Next y
                start1 = q + 1
                start = q + 1
                '****************Condition 2 - 4,5,6,11,12 is empty******************************************
            ElseIf exData(q, 2) <> "" And exData(q, 3) <> "" And exData(q, 4) = "" And exData(q, 5) = "" _
            And exData(q, 6) = "" And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" _
            And exData(q, 10) <> "" And exData(q, 11) = "" And exData(q, 12) = "" Then
 
                Dim tony As Integer = get3Value(start1, q)
                Dim id As Integer
 
                For y = start1 To q
 
                    If exData(y, 2) = "" And exData(y, 3) = "" And exData(y, 4) = "" And exData(y, 5) = "" _
                    And exData(y, 6) = "" And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _
                    And exData(y, 10) = "" And exData(y, 11) = "" And exData(y, 12) = "" Then
 
                        '1. All is empty
                        If y < endPTSIndex(0) Then
                            a = SplitFrontSZ(endPTSValue(0))
                            b = SplitBackSZ(endPTSValue(0))
                        ElseIf y > endPTSIndex(0) And y <= endPTSIndex(1) Then
                            a = SplitFrontSZ(endPTSValue(1))
                            b = SplitBackSZ(endPTSValue(1))
                        ElseIf y > endPTSIndex(1) And y <= endPTSIndex(2) Then
                            a = SplitFrontSZ(endPTSValue(2))
                            b = SplitBackSZ(endPTSValue(2))
                        ElseIf y > endPTSIndex(2) And y <= endPTSIndex(3) Then
                            a = SplitFrontSZ(endPTSValue(3))
                            b = SplitBackSZ(endPTSValue(3))
                        ElseIf y > endPTSIndex(3) And y <= endPTSIndex(4) Then
                            a = SplitFrontSZ(endPTSValue(4))
                            b = SplitBackSZ(endPTSValue(4))
                        ElseIf y > endPTSIndex(4) And y <= endPTSIndex(5) Then
                            a = SplitFrontSZ(endPTSValue(5))
                            b = SplitBackSZ(endPTSValue(5))
                        ElseIf y > endPTSIndex(5) And y <= endPTSIndex(6) Then
                            a = SplitFrontSZ(endPTSValue(6))
                            b = SplitBackSZ(endPTSValue(6))
                        ElseIf y > endPTSIndex(6) And y <= endPTSIndex(7) Then
                            a = SplitFrontSZ(endPTSValue(7))
                            b = SplitBackSZ(endPTSValue(7))
                        ElseIf y > endPTSIndex(7) And y <= endPTSIndex(8) Then
                            a = SplitFrontSZ(endPTSValue(8))
                            b = SplitBackSZ(endPTSValue(8))
                        ElseIf y > endPTSIndex(8) And y <= endPTSIndex(9) Then
                            a = SplitFrontSZ(endPTSValue(9))
                            b = SplitBackSZ(endPTSValue(9))
                        ElseIf y > endPTSIndex(9) And y <= endPTSIndex(10) Then
                            a = SplitFrontSZ(endPTSValue(10))
                            b = SplitBackSZ(endPTSValue(10))
                        Else
                            a = SplitFrontSZ(exData(q, 3))
                            b = SplitBackSZ(exData(q, 3))
                        End If
 
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 7) = exData(q, 7)
                        exData(y, 8) = exData(q, 8)
                        exData(y, 9) = exData(q, 9)
                        exData(y, 10) = e
                        exData(y, 11) = f
                        exData(y, 12) = exData(q, 12)
 
                    ElseIf exData(y, 2) = "" And exData(y, 3) <> "" And exData(y, 4) = "" And exData(y, 5) = "" _
                   And exData(y, 6) = "" And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _
                   And exData(y, 10) = "" And exData(y, 11) = "" And (exData(y, 12) = "" Or exData(y, 12) = "") Then
                        '2. 3 is NOT empty
                        a = SplitFrontSZ(exData(y, 3))
                        b = SplitBackSZ(exData(y, 3))
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 7) = exData(q, 7)
                        exData(y, 8) = exData(q, 8)
                        exData(y, 9) = exData(q, 9)
                        exData(y, 10) = e
                        exData(y, 11) = f
                        If exData(y, 12) = "" Then
                            exData(y, 12) = exData(q, 12)
                        End If
 
                    ElseIf exData(y, 2) <> "" And exData(y, 3) <> "" And exData(y, 4) = "" And exData(y, 5) = "" _
                    And exData(y, 6) = "" And exData(y, 7) <> "" And exData(y, 8) <> "" And exData(y, 9) <> "" _
                    And exData(y, 10) <> "" And exData(y, 11) = "" And exData(y, 12) = "" Then
 
                        '3. 4,5,6,11,12 is empty
                        a = SplitFrontSZ(exData(q, 3))
                        b = SplitBackSZ(exData(q, 3))
                        c = SplitFront(exData(q, 5))
                        d = SplitBack(exData(q, 5))
                        e = SplitFront(exData(q, 10))
                        f = SplitBack(exData(q, 10))
 
                        exData(y, 2) = exData(q, 2)
                        exData(y, 3) = a
                        exData(y, 4) = b
                        exData(y, 5) = c
                        exData(y, 6) = d
                        exData(y, 7) = exData(q, 7)
                        exData(y, 8) = exData(q, 8)
                        exData(y, 9) = exData(q, 9)
                        exData(y, 10) = e
                        exData(y, 11) = f
                        exData(y, 12) = exData(q, 12)
 
                    End If
                Next y
                start1 = q + 1
            End If
            '***********************CHANGE KRASDALE CODE TO UPC****************************************
            'Try
            Dim l As Integer = Len(exData(q, 0))
            If l = 6 Then
                For r = 0 To kcIndex
                    If fromKCtoUPC(r) = exData(q, 0) Then
                        Dim ln As Integer = Len(fromKCtoUPC(r + 1))
                        If ln > 6 Then
                            Dim t As String = fromKCtoUPC(r + 1)
                            exData(q, 0) = FormatNumber(fromKCtoUPC(r + 1), 0, , , False)
 
                            'Check the length of the UPC code
                            Dim lnt As Integer = Len(exData(q, 0))
                            If lnt = 10 Then
                                exData(q, 0) = "0" + exData(q, 0)
                            End If
 
                            Exit For
                        End If
                    End If
                Next r
            End If
            'Catch
            'End Try
            '*******************************************************************************************
        Next q
 
    End Sub
 
 
 
    'EVENT HANDLERS
    Private Sub Page_LoadExcel()
 
        exDataTable = New System.Data.DataTable
        '********************Create 16 columns with string as their type*******************************
        exDataTable.Columns.Add("UPC", String.Empty.GetType())
        exDataTable.Columns.Add("Description", String.Empty.GetType())
        exDataTable.Columns.Add("PK", String.Empty.GetType())
        exDataTable.Columns.Add("SizeUnit", String.Empty.GetType())
        exDataTable.Columns.Add("SizeMeasureID", String.Empty.GetType())
        exDataTable.Columns.Add("SLQty", String.Empty.GetType())
        exDataTable.Columns.Add("SLPrice", String.Empty.GetType())
        exDataTable.Columns.Add("Case Cost", String.Empty.GetType())
        exDataTable.Columns.Add("Deal", String.Empty.GetType())
        exDataTable.Columns.Add("Deal Cost", String.Empty.GetType())
        exDataTable.Columns.Add("Unit QTY", String.Empty.GetType())
        exDataTable.Columns.Add("Unit Cost", String.Empty.GetType())
        exDataTable.Columns.Add("Retail", String.Empty.GetType())
 
    End Sub
 
    'Auxilary Functions
    Private Function SplitFrontSZ(ByVal s As String) As String
 
        Dim ln = InStr(s, "-")
        If ln > 0 Then
            s = Mid(s, ln + 1, s.Length - 1)
        End If
 
        Dim l = InStr(s, " ")
        If l > 0 Then
            SplitFrontSZ = Mid(s, 1, l - 1)
        End If
 
        Dim ln1 = InStr(SplitFrontSZ, "z")
        If ln1 > 0 Then
            SplitFrontSZ = Mid(SplitFrontSZ, 1, ln1 - 1)
        End If
 
    End Function
    Private Function SplitBackSZ(ByVal s As String) As String
        Dim i As Integer, count As Integer, end1 As Integer, str1 As String
        Dim lgth As Integer = s.Length
 
        For i = 1 To lgth
            Dim l As String = Mid(s, i, 1)
            If l = " " Then
                str1 = Mid(s, (i + 1), lgth) 'Remove items before first space found.
                Exit For
            End If
        Next i
 
        Try
            count = 0 'Re-set count
            lgth = str1.Length 'Re-set length of the string
        Catch
        End Try
 
        For i = 1 To lgth
            Dim l As String = Mid(str1, i, 1)
            If l = " " Then
                count = count + 1
                If count = 1 Or count = 2 Then
                    end1 = i
                End If
            End If
        Next i
 
        If count = 1 Then
            SplitBackSZ = RTrim(Mid(str1, 1, end1))
        ElseIf count = 2 Then
            SplitBackSZ = RTrim(Mid(str1, 1, end1 - 1))
        Else
            SplitBackSZ = RTrim(Mid(str1, 1, end1))
        End If
 
        Dim ln1 = InStr(SplitBackSZ, "z")
        If ln1 = 0 And SplitBackSZ = "" Then
            Dim x As Integer, countChar As Integer
            For x = 0 To s.Length - 1
                Dim str2 As String = s.Substring(x, 1)
                If IsNumeric(str2) Then
                    countChar = countChar + 1
                Else
                    Try
 
                    Catch ex As Exception
                        SplitBackSZ = RTrim(Mid(s, (countChar + 1), s.Length - 5))
                        'Response.write(s.Length & s)
 
                    End Try
                    ' SplitBackSZ = RTrim(Mid(s, (countChar + 1), s.Length - 5))
                    Exit For
                End If
            Next
 
        End If
 
 
 
 
 
    End Function
    Private Function SplitFront(ByVal s As String) As String
        Dim l = InStr(s, "/")
        If l > 0 Then
            SplitFront = Mid(s, 1, l - 1)
        Else
            SplitFront = s
        End If
    End Function
    Private Function SplitBack(ByVal s As String) As String
        Dim l = InStr(s, "/")
        If l > 0 Then
            SplitBack = Mid(s, (l + 1), (Len(s) - 1))
        Else
            SplitBack = s
        End If
    End Function
    Private Function SplitZs(ByVal s As String) As String
        Dim l = InStr(s, "/")
        If l > 0 Then
            SplitZs = Mid(s, (l + 1), (Len(s) - 1))
        Else
            SplitZs = s
        End If
    End Function

Open in new window

ScreenHunter-01-Oct.-13-12.15.gif
ScreenHunter-02-Oct.-13-12.15.gif
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 300 total points
ID: 22704052
This will work if the values are always the 2nd and 4th characters and 1 character in length:

sB1G1 = "B1G1"
sValue1 = sB1G1.substring(1,1)
sValue2 = sB1G1.substring(3,1)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

635 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