Announcement

Collapse
No announcement yet.

Match Text String & Split Output

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match Text String & Split Output

    I have a spreadsheet with Approx 900 rows of information that has been entered incorrectly.

    Spreadsheet has 6 columns.
    Contact Name
    Contact Number
    Company Name
    Company Account Number
    Order Date
    Order Numbers

    Every Order should have its own line - However I have approx 900 rows where the order numbers have multiple entries instead of single entries.

    All the order numbers end "LO" and there all 8 digits long.

    I wanted to know if its possible to use excel to look for all instances of "LO" in the column Order Numbers and delete the original Row and replace it with 3 rows with the same information.

    Example:
    Attached to this post!

    Is this possible? I have 15 historic files each approx 35000 rows and I suspect there are more errors
    Attached Files

  • #2
    Re: Match A Text String And Then Split The Output

    w00t,

    It looks like you have two problems. How to separate the multiple orders into separate orders and how to make separate rows containing those new orders.

    As for separating the orders, try text to columns from the Excel menu. This should be simple enough to figure out. The second problem could be a bigger problem. If you have some files with 35K records, you could end up with more than 65K records and exceed the row limit for one worksheet. Can you live with extra columns for the new order numbers?

    Jim

    Comment


    • #3
      Re: Match A Text String And Then Split The Output

      Hi,

      Not an elegant one. But..

      Sub test()
      Dim lRow As Long
      Dim i As Long, x

      lRow = Range("F" & Rows.Count).End(xlUp).Row

      For i = lRow To 2 Step -1
      If InStr(1, Cells(i, 6).Value, "-") > 0 Then
      x = Split(Cells(i, 6), "-")
      Rows(i + 1 & ":" & i + UBound(x)).Insert
      Range(Cells(i, 1), Cells(i + UBound(x), 5)).FillDown
      Cells(i, 6) = x(0)
      For j = 1 To UBound(x)
      Cells(i + j, 6) = Trim(x(j))
      Next
      End If
      Next
      End Sub


      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Match Text String & Split Output

        I Understand and can see the example given does work.

        However the example.xls is just that.

        I need a way to select only text ending "LO" and then count back 8 chars and split this into a new row only.

        Some of the records I have in this spreadsheet have Text e.g. spoke to customer and gave 3 reference numbers 123456LO for the Plane Details, 123457LO for the Train booking and 123458LO for the Car Booking.

        Using the above code is governed by the text all being the same format and this is sometimes not always the case.

        Need to match on the last two chars only and then paste this into a new cell.

        Really do appreciate the response and thanks in advance for any further replies!

        Comment


        • #5
          Re: Match Text String & Split Output

          May be..

          Sub test_v1()
          Dim lRow As Long
          Dim i As Long, x

          lRow = Range("F" & Rows.Count).End(xlUp).Row

          For i = lRow To 2 Step -1
          If InStr(1, Cells(i, 6).Value, "LO") > 0 Then
          x = Split(Cells(i, 6), "LO")
          Rows(i + 1 & ":" & i + UBound(x) - 1).Insert
          Range(Cells(i, 1), Cells(i + UBound(x) - 1, 5)).FillDown
          Cells(i, 6) = Right(x(0), 6) & "LO"
          For j = 1 To UBound(x) - 1
          Cells(i + j, 6) = Trim(Right(x(j), 6)) & "LO"
          Next
          End If
          Next
          End Sub


          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Match Text String & Split Output

            Fantastic!

            One final one on this .

            Any chance I can be cheeky and ask for an explanation on the code ?

            Figure its nice to have an answer but even better to have an explanation for future reference!

            I am not a Master - In fact i'm barely a white belt when it comes to VBA : D : D : D

            Comment


            • #7
              *Bump* Re: Match Text String & Split Output *Bump*

              I've still got an issue with the following.

              Code:
               Sub test_v1() 
                  Dim lRow    As Long 
                  Dim i       As Long, x 
                   
                  lRow = Range("F" & Rows.Count).End(xlUp).Row 
                   
                  For i = lRow To 2 Step -1 
                      If InStr(1, Cells(i, 6).Value, "LO") > 0 Then 
                          x = Split(Cells(i, 6), "LO") 
                          Rows(i + 1 & ":" & i + UBound(x) - 1).Insert 
                          Range(Cells(i, 1), Cells(i + UBound(x) - 1, 5)).FillDown 
                          Cells(i, 6) = Right(x(0), 6) & "LO" 
                          For j = 1 To UBound(x) - 1 
                              Cells(i + j, 6) = Trim(Right(x(j), 6)) & "LO" 
                          Next 
                      End If 
                  Next 
              End Sub
              Example spreadsheet attached.

              For some reasons its adding spaces and copying the reference numbers against different company names etc.
              It is not happening on all records but a fair few!

              Please Help!

              Thanks In Advance
              Attached Files

              Comment


              • #8
                Re: *Bump* Re: Match Text String & Split Output *Bump*

                Hi,

                Sub test_v2()
                Dim lRow As Long
                Dim i As Long, x

                lRow = Range("F" & Rows.Count).End(xlUp).Row

                For i = lRow To 2 Step -1
                x = Split(Cells(i, 6), "LO")
                If UBound(x) > 1 Then
                Rows(i + 1 & ":" & i + UBound(x) - 1).Insert
                Range(Cells(i, 1), Cells(i + UBound(x) - 1, 5)).FillDown
                Cells(i, 6) = Trim(Right(x(0), 6)) & "LO"
                For j = 1 To UBound(x) - 1
                Cells(i + j, 6) = Trim(Right(x(j), 6)) & "LO"
                Next
                Else
                Cells(i, 6) = Trim(Right(x(0), 6)) & "LO"
                End If
                Next
                End Sub


                HTH
                Kris

                ExcelFox

                Comment


                • #9
                  Re: Match Text String & Split Output

                  KrishnaKumar,

                  I really appreciate the responses on this!!!

                  The second version of the code gives an error "Subscript out of range"

                  Is this a problem with the code or a problem with My spreadsheet?

                  Comment


                  • #10
                    Re: Match Text String & Split Output

                    in which line?
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: Match Text String & Split Output

                      Shows as run time error '9' :
                      Subscript out of range.

                      Is this enough info or can you tell me how to find what you need?

                      Comment


                      • #12
                        Re: Match Text String & Split Output

                        When you debug the error, which line is highlighted?
                        Kris

                        ExcelFox

                        Comment


                        • #13
                          Re: Match Text String & Split Output

                          It's not highlighting a line when I use Debug. Just keeps on giving the same error.

                          Could this be due to the text size?

                          In some instances there are up to 400 characters in the Reference Field where the person has wrote "War And Peace" for no apparent reason!

                          Comment


                          • #14
                            Re: Match Text String & Split Output

                            attach the workbook with different pattern of data. you can zip the file to reduce the size.
                            Kris

                            ExcelFox

                            Comment


                            • #15
                              Re: Match Text String & Split Output

                              Kris,

                              Massive thanks for all your help I realised what was causing the Subscript out of range error.

                              I combined all the entries from all my spreadsheets into one - when I leave it as 5000 records each it comes back all fine!

                              Other file was 19286 records which was compiled from 23 files in total.

                              I can live with Splitting the file as long as the Code works and it does!

                              Thanks for this I really appreciate your help!

                              Comment

                              Working...
                              X