Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Match Text String & Split Output

  1. #1
    Join Date
    3rd October 2004
    Posts
    21

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Posts
    966

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Match A Text String And Then Split The Output

    Hi,

    Not an elegant one. But..

    VB:
    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

  4. #4
    Join Date
    3rd October 2004
    Posts
    21

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Match Text String & Split Output

    May be..

    VB:
    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

  6. #6
    Join Date
    3rd October 2004
    Posts
    21

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd October 2004
    Posts
    21

    *Bump* Re: Match Text String & Split Output *Bump*

    I've still got an issue with the following.

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: *Bump* Re: Match Text String & Split Output *Bump*

    Hi,

    VB:
    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

  9. #9
    Join Date
    3rd October 2004
    Posts
    21

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Match Text String & Split Output

    in which line?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. String Split in vba
    By nojy in forum Excel General
    Replies: 7
    Last Post: December 27th, 2010, 20:16
  2. Split String At First Delimiter Only
    By MaxTO in forum Excel General
    Replies: 7
    Last Post: November 11th, 2008, 11:12
  3. Split vbCrLf String
    By jonny in forum Excel General
    Replies: 4
    Last Post: January 3rd, 2007, 22:19
  4. Evaluating string functions to output text
    By dister520 in forum Excel General
    Replies: 2
    Last Post: April 22nd, 2006, 01:41
  5. Split RTD string to Array?
    By Håkan in forum Excel General
    Replies: 2
    Last Post: September 22nd, 2004, 23:53

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno