Remove numbers after a string of text

[neelymedlock]

Looking for a formula to remove numbers listed after a name.

Ex

[TABLE="width: 195"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Aagard, Erik 0009515[/TD]
[/TR]
[TR]
[TD]Abala, Judy 0010752[/TD]
[/TR]
[TR]
[TD]Abbey, Denis 0016052[/TD]
[/TR]
[TR]
[TD]Abbuhl, Karen 0006640[/TD]
[/TR]
[TR]
[TD]Abdelnour, Medhat 0013276

Thank you[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Are all your number exactly 7 digits?
If so, a formula like this would work:

=LEFT(A1,LEN(A1)-8)

 

[neelymedlock]

I believe so, it worked so far, thank you!

 

[Joe4]

You are welcome!

 

[neelymedlock]

Okay, so it worked for A1, but then when I drag the formula down, it keeps looking at cell A1, instead of progressing to the next cell. Appreciate any help.

 

[Joe4]

Okay, so it worked for A1, but then when I drag the formula down, it keeps looking at cell A1, instead of progressing to the next cell.

The formula should automatically increment.
If you look at the formula in the next cell, does it look correct?

I suspect maybe you have the Calculations set to Manual. If you press F9, does it update the values shown?
If so, then that is the reason.

 

[neelymedlock]

On my sample sheet it works fine. On the true data sheet, it's the same formula in all the cells looking at A1, even when pressing F9

 

[Joe4]

On my sample sheet it works fine. On the true data sheet,

OK, so what is different about your "True" data sheet.
Is it in a different Excel format?
Where did it originate from?
Are you using a Mac version of Excel?

Try this:
Enter the formula in the first cell:

=LEFT(A1,LEN(A1)-8)

and then manually enter the incremented formula in the second cell:

=LEFT(A[COLOR=#ff0000][B]2[/B][/COLOR],LEN(A[B][COLOR=#ff0000]2[/COLOR][/B])-8)

Then, highlight BOTH of those cells at the same time, and try dragging down for all rows.
Does that work?

 

[neelymedlock]

Got it! The formula progressed, but the result was the same name as the first line, but I had him highlight the column with the formula and press F9 and it worked. thanks!

 

[Joe4]

Note that if your worksheet to set to Manual Calculation, you can change that to Automatic in the Options, so you do not need to press F9 every time.