Text File Problems

[Joe_Toz]

I am hoping (once again!) somebody out there can help me. I have a messy text file that I am trying to use. Unfortunately, nothing further can be done to improve it from the source. Using Excel seems to be my only option. The text file contains thousands of customer records. Each record begins with a last name in capital letters. The problem I have is that some of the records are combined on 1 line. I need to have each record on a separate line. I would like to find a way to separate those combined records onto individual lines. If this cannot be done, then my next best option would be to somehow identify these lines with combined records (e.g. – placing an “X” in an adjacent cell).

Here is a sample of my file:
*KAHILAINEN, Janice; 1971 (See Nelson, Janice)
*KAHL, Bernadine (Berny); 1974 (See Newman, Ms. Bernadine) *KAHL, Cynthia L.; 1976 (See Kline, Mrs. Cynthia L.)
KAHLE, Dennis B.; 1965; Co-Owner, Stuart Photo, 5555 SE Federal Hwy., Stuart, FL 55555; r: same, 555 555-5555; Pamela Matlack; Jason (Dec)
KAHLE, Gary A.; 1962; r: 555 Sao Paulo, Punta Gorda, FL 55555 KAHLE, Pamela (Pamela Matlack); 1965; Co-Owner, Stuart Photo, 5555 SE Federal Hwy., Stuart, FL 55555; r: same, 555 555-5555; Dennis; Jason (Dec.)
*KAHLER, Carol; 1970 (See Hemingway, Mrs. Carol)
*KAHLER, Jennifer L.; 1973 (See Miller, Mrs. Jennifer L.) KAHLER, Michael; 1969; r: 5555 Frances Dr., Delray Bch., FL 55555
KAHLER, Sandy; 1966 (See Gleeson, Mrs. Sandy P.)
KAHN, Evan H.; 2002; r: 5555 Steven Rd, Boynton Bch., FL 55555, 555 555-5555
*KAISER, Judith A.; 1960 (See Edens, Mrs. Judith)
KALAIN, Diana; 1967 (See Efaw, Mrs. Diana R.)
*KACHUBA, Susan; 1963 (See Mahone, Mrs. Susan) KACZMAREK, Elizabeth L. (Elizabeth Cantley); 1978; Homemaker; r: 5555 Chevoit Dr., Brentwood, TN 55555, 555 555-5555; Walt; Christopher Blaine, Tyler Michael, Logan Daniel KAEDING, Mrs. Barbara J. (Barbara J. Lewis); 1973; Sal es Assoc., Carmax, 555 Hige Ridge Rd., Boynton Bch., FL 55555; r: 555 Lake Ave. Apt. 5, Lake Worth, FL 55555, 555 555-5555 KAFTAN, Ken L.; 1980; r: 555 S. 55th St., Lantana, FL 55555, 555 555-5555

If anybody out there has any ideas, I sure would appreciate it.
Thanks!

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim x As Long
    Dim r As Long
    Dim Arr As Variant
    Dim i As Integer
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    Rng.Replace What:="~*", Replacement:=""
    Set ShNew = Worksheets.Add
    r = 1
    For x = 1 To Rng.Rows.Count
        With ShNew
            With .Cells(r, 1)
                .Value = Rng.Cells(x, 1).Value
                .Value = WorksheetFunction.Substitute(.Value, Left(.Value, 2), "|" & Left(.Value, 2))
                Arr = Split(.Value, "|")
            End With
            For i = 1 To UBound(Arr)
                If i > 1 Then
                    .Cells(r, 1).Value = Arr(i)
                Else
                    .Cells(r, 1).Value = WorksheetFunction.Substitute(Arr(i), "|", "")
                End If
                r = r + 1
            Next i
        End With
    Next x
End Sub

The pattern seems to be that repeated names have the same first two characters as the first name. It seems to work on your sample data.

 

[Joe_Toz]

Dude,

You are the man! Wow, that works great! I have a few questions: What happens if the first two characters are not the same? Will the macro simply bypass the line or will it stop running? There should not be many instances where the first two characters are not the same, but I was wondering if there is a way to either handle or identify these rows in the macro.

Thanks again - truly amazing work!

JOE

 

[Andrew Poulsom]

Nothing will happen (ie no parsing) if the repeated names don't contain the same first two characters. You will have to do those manually.