Solved

split function

Posted on 2008-10-13
8
247 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:sevensnake77
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 9

Author Comment

by:sevensnake77
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:sevensnake77
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:sevensnake77
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

770 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