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]

How about

Sub Excel1991()
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
        .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub

 

[Excel1991]

This works great. Thank you.

There are a few numbers that I think may be stored as text as they were not filled (there are only a few so it is manageable), but I was wondering (for future possibly longer lists) what I would have to do to make sure they are converted as well? The "numbers" in my lists are codes and they are usually represented by 5 numeric digits. However, sometimes they may appear as "G0999" or "99999-25".

 

[Fluff]

Both of those are text, not numbers. Do any of the values you want to keep end with a number and do any of the values you want to remove end in anything other than a number?

 

[Excel1991]

None of the values i wish to keep end in a number. Occasionally there may be a numeric code (which i am trying to remove) that has a trailing letter such as "99999R"

 

[Fluff]

In that case you need to come up with a rule that will identify those values you wish to remove, but will not affect the values you wish to remove.
So for instance will the 2nd character always be numeric?

 

[Excel1991]

ROBBY

99999

99999R

99999-25

G0459

GEORGE

<tbody>
</tbody>

Just to be more clear, I am trying to fill the name robby down until it hits "george" and then the pattern would continue. I am trying to ignore those codes in between if that is possible? The codes will always be one of these four variations.

 

[Fluff]

So is it safe to delete all values that have a number as the 2nd character?

 

[Fluff]

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

 

[Excel1991]

This worked remarkably! Thank you for your help. Amazing how much a code can simplify a project