Remove all Numbers from list

[Excel1991]

Hi all,

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

ROBBY
18205
19206
GEORGE
16573
ANDERSON
23456
26752
47658

<tbody>
</tbody>

I am trying to get the list so that it looks as follows:

ROBBY

ROBBY

ROBBY

GEORGE

GEORGE

ANDERSON

ANDERSON

ANDERSON

ANDERSON

<tbody>
</tbody>

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