Remove numbers after a string of text

neelymedlock

New Member
Joined
Jun 15, 2018
Messages
8
Looking for a formula to remove numbers listed after a name.

Ex

Aagard, Erik 0009515
Abala, Judy 0010752
Abbey, Denis 0016052
Abbuhl, Karen 0006640
Abdelnour, Medhat 0013276

Thank you

<colgroup><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Are all your number exactly 7 digits?
If so, a formula like this would work:
Code:
=LEFT(A1,LEN(A1)-8)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
 

neelymedlock

New Member
Joined
Jun 15, 2018
Messages
8

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2018
Messages
8

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=LEFT(A1,LEN(A1)-8)
and then manually enter the incremented formula in the second cell:
Code:
=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

New Member
Joined
Jun 15, 2018
Messages
8
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,019
Messages
5,526,290
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top