Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

split function

Posted on 2008-10-13
8
Medium Priority
?
264 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 900 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 300 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 300 total points
ID: 22703262
Probably a String.Replace() method will fit for you:
http://msdn.microsoft.com/en-us/library/fk49wtc1.aspx 
0
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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 …

722 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