Repeat Function for editing mulitple cells

[Deep Nine]

Hello - need some help, could be an easy one.

I'm using Excel 2000 and have been given a spreadsheet with over 5000 rows to check and audit as it will be used to feed into a Macro. I've noticed that every entry in one of the columns, has an extra digit "1" at the end the text that is not needed. E.g. entry is "ADF-000012341" and I only need "ADF-00001234".

To start with I double clicked on the very first cell and at the end of the text, deleted the digit '1' and moved to the next cell below, did the same, and so on and so forth down and down and down.

Haven't got far and thinking, there must be a way Excel can learn this pattern and do it automatically?

Any help would be much appreciated guys and gals.......

[davesexcel]

Here's a formula you could use to take the last digit out
=LEFT(M1,LEN(M1)-1)
then you could copy and paste special values into the original range
Highlite the entire range to place the formula, then write the formula and hit
Ctrl enter
the formula will enter the entire highlited range
The formula above works if the value was in M1

[Deep Nine]

Here's a formula you could use to take the last digit out
=LEFT(M1,LEN(M1)-1)
then you could copy and paste special values into the original range
Highlite the entire range to place the formula, then write the formula and hit
Ctrl enter
the formula will enter the entire highlited range
The formula above works if the value was in M1

Thanks Dave - not wanting to sound like a complete donut but how/where do I enter/execute the formula without losing the original text in each cell???

[daddylonglegs]

Dave's assuming you have the data in column M so you'd enter his suggested formula in any other empty column at row 1, ideally N1, and then copy down the column.

If you use a column which is immediately adjacent to your data you can copy down quickly all 5000 rows this way:

Put formula in N1
Put cursor on the bottom right corner of N1 until you see the "fill-handle", a black +, double-click this

Note: if all of your entries are the same number of characters, and you just want to remove the last character then you can do this with "Text to columns" functionality.

Assume that data is in A1:A5000

Select column A
From "Data" menu choose "Text to columns"
Choose "Fixed width" > Next
Follow the instruction to "CREATE a line break" one character from the end > Next
Click on column containing the single character and check "do not import column (skip)"
Finish

[Deep Nine]

That did the trick guys, both solutions worked, your gents and scholars.

Cyber beers heading your way.

:tip: