Macro: How to find & replace periods if they are preceded by characters

[halinc]

Hi,
I have a set of data that I am splitting with a macro based on period placement. For example, this:
1. Joe Smith 2. Mary White 3. Ted Jones

is split into:
Joe Smith
Mary White
Ted Jones


My macro works fine except when someone has a period in their name, which creates a situation such as:
1. Joe Smith 2. J.R. Smith 3. Mary White

split into:
Joe Smith
J
R
Smith
Mary White


Is there a way I can have my macro search for periods that are preceded by characters (rather than numbers)? And then replace those periods with a space or some other character?

Thanks for any help!
Hal

 

[VBA Geek]

1. Joe Smith 2. Mary White 3. Ted Jones - the string on the left is all in the same cell?

 

[halinc]

Yes, they are in the same cell (and are actually separated by carriage returns in the cell). This is part of a bigger macro. My data looks something like this (except more rows/columns):

[TABLE="class: grid, width: 275"]
<tbody>[TR]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Role[/TD]
[/TR]
[TR]
[TD]1. Joe Smith
2. Mary White
3. Ted Jones
4. J.R. Smith[/TD]
[TD]1. Former Colleague
2. Colleague
3. Colleague
4. Former Colleague[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton
2. George Bush[/TD]
[TD]1. Colleague
2. Colleague[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

This will split your string which is in A1, in C1:C4

Sub RTGF()
    Dim Reg As Object
    Set Reg = CreateObject("VBScript.RegExp")
    Dim g
    Dim t As String
    t = Mid([A1], InStr(Range("A1"), " ") + 1)
    With Reg
        .Global = 1
        .IgnoreCase = 1
        .Pattern = "\s?[0-9]+\.\s"
        g = Split(.Replace(t, "&"), "&")
    End With
    Range("C1").Resize(UBound(g) + 1).Value = _
                            Application.Transpose(g)
    Set Reg = Nothing
End Sub

 

[halinc]

Looking again at my macro, I get rid of the carriage returns, so my data would actually look like:

[TABLE="class: grid, width: 400"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith
[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush
[/TD]
[/TR]
</tbody>[/TABLE]

And then I just use text-to-columns, splitting based on periods.

 

[VBA Geek]

if you wish to split in adjacent columns then change

Range("C1").Resize(UBound(g) + 1).Value = _
Application.Transpose(g)

to

Range("C1").Resize(, UBound(g) + 1).Value = g

Looking again at my macro, I get rid of the carriage returns, so my data would actually look like:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush[/TD]
[/TR]
</tbody>[/TABLE]

And then I just use text-to-columns, splitting based on periods.

 

[halinc]

Ok, thanks! I think I can retrofit that into my macro.. do you know how I would get that to cycle down all the rows with data? So it would change this:
[TABLE="class: grid, width: 396"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush[/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith
[/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush[/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush
[/TD]
[/TR]
</tbody>[/TABLE]

into this:

[TABLE="class: grid, width: 915"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[TD="align: left"]Joe Smith[/TD]
[TD="align: left"]Mary White[/TD]
[TD="align: left"]Ted Jones[/TD]
[TD="align: left"]J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush
[/TD]
[TD="align: left"]Bill Clinton[/TD]
[TD="align: left"]George Bush[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[TD="align: left"]Joe Smith[/TD]
[TD="align: left"]Mary White[/TD]
[TD="align: left"]Ted Jones[/TD]
[TD="align: left"]J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith[/TD]
[TD="align: left"]Joe Smith[/TD]
[TD="align: left"]Mary White[/TD]
[TD="align: left"]Ted Jones[/TD]
[TD="align: left"]J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush[/TD]
[TD="align: left"]Bill Clinton
[/TD]
[TD="align: left"]George Bush[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1. Joe Smith2. Mary White3. Ted Jones4. J.R. Smith
[/TD]
[TD="align: left"]Joe Smith[/TD]
[TD="align: left"]Mary White[/TD]
[TD="align: left"]Ted Jones[/TD]
[TD="align: left"]J.R. Smith[/TD]
[/TR]
[TR]
[TD]1. Bill Clinton2. George Bush[/TD]
[TD="align: left"]Bill Clinton
[/TD]
[TD="align: left"]George Bush[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Sub RTGF()
    Dim Reg As Object
    Set Reg = CreateObject("VBScript.RegExp")
    Dim g
    Dim t As String
    Dim k As Long: k = 0
    While Not Range("A1").Offset(k).Value = vbNullString
        t = Mid(Range("A1").Offset(k).Value, InStr(Range("A1").Offset(k).Value, " ") + 1)
        With Reg
            .Global = 1
            .IgnoreCase = 1
            .Pattern = "\s?[0-9]+\.\s"
            g = Split(.Replace(t, "&"), "&")
        End With
        Range("A1").Offset(k, 2).Resize(, UBound(g) + 1).Value = g
        k = k + 1
    Wend
    Set Reg = Nothing
    MsgBox "Data successfully split."
End Sub

 

[Rick Rothstein]

This code, which works with your original data with the Line Feed characters (you called them Carriage Returns) still in place, will process Column A from Row 1 down to the last row in Column A with data....

Sub RTGF2()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("B1:B" & LastRow)
    .Value = Evaluate("Char(10)&A1:A" & LastRow)
    .Replace vbLf & "*" & ".", "&", xlPart
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "&"
    .Delete xlShiftToLeft
  End With
End Sub

 

[halinc]

I'm sorry, I should have mentioned this earlier--there are some lines that have only one name, with no number in front. VBA Geek, when I use your code, it cuts off the first name. Rick, when I use your code, it doesn't output anything for those rows. Is there a way to get those names output as well?