Text Editing (2002)

Topic

New Reply

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?

Reply | Quote

Replies

Does this do what you want:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

It partially worked. I have attached file…

As always, thanks for the help.

Reply | Quote

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

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

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.

Reply | Quote

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.

Reply | Quote

Thanks to you and Legare. Worked like a charm!

Reply | Quote

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.

Reply | Quote