Solved

split function

Posted on 2008-10-13
8
242 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
Comment Utility
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
Comment Utility
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
Comment Utility
Probably a String.Replace() method will fit for you:
http://msdn.microsoft.com/en-us/library/fk49wtc1.aspx
0
 
LVL 9

Author Comment

by:sevensnake77
Comment Utility
To Arthur_Wood:
I want to remove the B and the G and just have a value for 1
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Assisted Solution

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

Author Comment

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now