Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Editing (2002)

    I have some "warning data" that I need to summarize. Unfortunately, some of the entries are combined. I am pasting an example below:

    Date Warning Data Frequency
    200503 organize was found in charge_desc; intern was found in charge_desc 2
    __________________________________________________ ___________________________________

    When a semi colon is encountered, I'd like to move what's to the right of it down to another row and copythe Date and Frequency. For instance, this data above would now appear in 2 different rows as below:
    __________________________________________________ ____________

    200503 organize was found in charge_desc; 2
    200503 intern was found in charge_desc 2

    Is there an automated way of doing this?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    Does this do what you want:

    <pre>Public Sub SplitData()
    Dim lLastRow As Long, I As Long
    Dim strWk As String, strnew As String
    lLastRow = Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    strWk = Range("A1").Offset(I, 0).Value
    Do While InStr(strWk, ";") > 0
    strnew = Left(strWk, InStr(strWk, " ") - 1) & " "
    strnew = strnew & Right(strWk, Len(strWk) - InStrRev(strWk, ";"))
    Range("A1").Offset(I + 1, 0).EntireRow.Insert
    Range("A1").Offset(I + 1, 0).Value = strnew
    strWk = Left(strWk, InStrRev(strWk, ";") - 1)
    Range("A1").Offset(I, 0).Value = strWk
    Loop
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    I inserted as a maco, ran and could not detect that it did anything... Should I try something different?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Editing (2002)

    Legare's macro assumes that the strings are in column A. If they are in another column, adjust the macro accordingly.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    What column are the strings in? Since you did not say, I wrote the macro to work in column A. If the strings are in a different column, the macro will need to be adjusted. If you still can't get it to work, please upload a workbook that can be used to test with.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    It partially worked. I have attached file...

    As always, thanks for the help.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    Here is a macro that works with the workbook you uploaded:

    <pre>Public Sub SplitData()
    Dim lLastRow As Long, I As Long
    Dim strWk As String, strnew As String
    Application.ScreenUpdating = False
    lLastRow = Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    strWk = Range("A1").Offset(I, 0).Value
    Do While InStr(strWk, ";") > 0
    strnew = Trim(Right(strWk, Len(strWk) - InStrRev(strWk, ";")))
    Range("A1").Offset(I + 1, 0).EntireRow.Insert
    Range("A1").Offset(I + 1, 0).Value = strnew
    Range("A1").Offset(I + 1, 1).Value = Range("A1").Offset(I, 1).Value
    Range("A1").Offset(I + 1, 2).Value = Range("A1").Offset(I, 2).Value
    strWk = Left(strWk, InStrRev(strWk, ";") - 1)
    Range("A1").Offset(I, 0).Value = strWk
    Loop
    Next I
    Application.ScreenUpdating = True
    End Sub
    </pre>


    One point. The values in columns B and C are entered as Text, but the columns are formatted as general (this can happen when data is imported). My macro will enter the new values in these columns as numbers. You should either convert the values that are there to numbers, or format the columns as Text before you run the macro. I have attached your workbook with the macro included.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Editing (2002)

    Try this:

    Public Sub SplitData()
    Dim lLastRow As Long, I As Long
    Dim strWk As String
    Application.ScreenUpdating = False
    lLastRow = Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    strWk = Range("A1").Offset(I, 0).Value
    Do While InStr(strWk, ";") > 0
    Range("A1").Offset(I + 1, 0).EntireRow.Insert
    Range("A1").Offset(I + 1, 0).Value = _
    Trim(Mid(strWk, InStrRev(strWk, ";") + 1))
    Range("B1").Offset(I + 1, 0).Value = _
    Range("B1").Offset(I, 0).Value
    Range("C1").Offset(I + 1, 0).Value = _
    Range("C1").Offset(I, 0).Value
    strWk = Trim(Left(strWk, InStrRev(strWk, ";") - 1))
    Range("A1").Offset(I, 0).Value = strWk
    Loop
    Next I
    Application.ScreenUpdating = True
    End Sub

    If you want the values in columns B and C to be interpreted as text, format them as text before running the macro.

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Editing (2002)

    Thanks to you and Legare. Worked like a charm!

Posting Permissions

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