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

 

[halinc]

Actually, I think I got it.. looks like the code was using "&" to denote where to parse? So I replaced with #, and it seems to work fine. Thank you both so much for all the help!!

 

[Rick Rothstein]

Actually, I think I got it.. looks like the code was using "&" to denote where to parse? So I replaced with #, and it seems to work fine. Thank you both so much for all the help!!

Here is my code modified so that you never have to worry about what characters may be embedded within the text...

Sub RTGF2()
  Dim LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("B2:B" & LastRow)
    .Value = Evaluate("Char(10)&IF(NOT(ISNUMBER(-LEFT(B2:B" & LastRow & "))),""."","""")&B2:B" & LastRow)
    .Replace vbLf & "*" & ".", Chr$(1), xlPart
    .TextToColumns Range("B2"), xlDelimited, , , False, False, False, False, True, Chr$(1)
    .Delete xlShiftToLeft
  End With
  LastCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
  Range("C1").Resize(, LastCol - 2).Value = Range("B1").Value
  Columns("A").Resize(, LastCol).AutoFit
End Sub

 

[halinc]

Thank you!
If you have time, I have one more question. It seems like I should be able to do this by modifying your code, but I'm having trouble with it. I'd like something that would look through each column and put "blank" in any blank cell (not a big deal to just manually filter for blanks and fill down, but a macro would be cleaner).

So column A would be used to set how far down each other column the macro should check, then it should go through the other columns and put "blank" in the empty cells (B4, C5, D2, E5):

[TABLE="class: grid, width: 500"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="align: center"]Case ID
[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Role[/TD]
[TD="align: center"]Emp ID[/TD]
[TD="align: center"]Practice[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]Joe[/TD]
[TD]Mgr[/TD]
[TD][/TD]
[TD]East[/TD]
[/TR]
[TR]
[TD]8789[/TD]
[TD]1. Ted
2. Frank[/TD]
[TD]1. Emp
2. Mgr
[/TD]
[TD]1.
2. 45678[/TD]
[TD]1. West
2. East
[/TD]
[/TR]
[TR]
[TD]421[/TD]
[TD][/TD]
[TD]Emp
[/TD]
[TD]87546[/TD]
[TD]East
[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD]43215[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]1548[/TD]
[TD]1. Mike
2. Hal
3. Lisa[/TD]
[TD]1. Emp
2.
3 . Mgr[/TD]
[TD]1.
2. 11234
3. 45687[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you!
If you have time, I have one more question. It seems like I should be able to do this by modifying your code, but I'm having trouble with it. I'd like something that would look through each column and put "blank" in any blank cell (not a big deal to just manually filter for blanks and fill down, but a macro would be cleaner).

So column A would be used to set how far down each other column the macro should check, then it should go through the other columns and put "blank" in the empty cells (B4, C5, D2, E5):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Case ID
[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Role[/TD]
[TD="align: center"]Emp ID[/TD]
[TD="align: center"]Practice[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Joe[/TD]
[TD]Mgr[/TD]
[TD][/TD]
[TD]East[/TD]
[/TR]
[TR]
[TD]8789[/TD]
[TD]1. Ted
2. Frank[/TD]
[TD]1. Emp
2. Mgr[/TD]
[TD]1.
2. 45678[/TD]
[TD]1. West
2. East[/TD]
[/TR]
[TR]
[TD]421[/TD]
[TD][/TD]
[TD]Emp[/TD]
[TD]87546[/TD]
[TD]East[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD]43215[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]1548[/TD]
[TD]1. Mike
2. Hal
3. Lisa[/TD]
[TD]1. Emp
2.
3 . Mgr[/TD]
[TD]1.
2. 11234
3. 45687
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Not sure whose code you are thinking of embedding this in (when you have more than one contributor, you should mention who you are thanking or responding to), but the following code line should work in anyone's code...

Range("B1:E" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).Value = "blank"

Note: If you were referring to my macro, then you can replace what I highlighted in red with LastRow as I calculated that value earlier in my code and stored it in the LastRow variable.

 

[halinc]

Rick--that works great, thank you!

And many thanks to both you and VBA Geek!