Solved

split function

Posted on 2008-10-13
8
255 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
[X]
Welcome to Experts Exchange

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

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

Accepted Solution

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

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

Assisted Solution

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

Assisted Solution

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

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
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 …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

734 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