Remove all Numbers from list

[Excel1991]

Hi all,

I have a list in excel that looks similar to this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ROBBY[/TD]
[/TR]
[TR]
[TD]18205[/TD]
[/TR]
[TR]
[TD]19206[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD]16573[/TD]
[/TR]
[TR]
[TD]ANDERSON[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[/TR]
[TR]
[TD]26752[/TD]
[/TR]
[TR]
[TD]47658[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to get the list so that it looks as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ROBBY[/TD]
[/TR]
[TR]
[TD]ROBBY[/TD]
[/TR]
[TR]
[TD]ROBBY[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD]ANDERSON[/TD]
[/TR]
[TR]
[TD]ANDERSON[/TD]
[/TR]
[TR]
[TD]ANDERSON[/TD]
[/TR]
[TR]
[TD]ANDERSON[/TD]
[/TR]
</tbody>[/TABLE]

I was thinking I could try and remove all the numbers and then do a formula to fill the text down until it reaches the next filled cell. Anyone know how to do this or if there is an easier way? Thanks!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

If the answer to the above question is yes, then try

Sub Excel1991()
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(isnumber(--mid(@,2,1)),"""",@)", "@", .Address))
        .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub

You can compact your code slightly by putting the R1C1 formula inside the Evaluate function call and removing the SpecialCells code line completely (the Value property can receive a formula value directly)...

Sub Excel1991()
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(isnumber(--mid(@,2,1)),""=r[-1]c"",@)", "@", .Address))
        .Value = .Value
    End With
End Sub