can i delete the last 3 characters in each cell in a column in 1 .

H

homer

I have a column of names followed by a number. Obviously the names are of
different lengths but the numbers are consistent, so i need to remove say 8
chars from the right of each filed in the column. I'm sure I have done this
before but can't remember how! D'oh!
 
D

Duke Carey

In an empty column, enter this formula (assuming your names are in column A
and all the numbers are 8 characters long)

=LEFT(A1,LEN(A1) - 8) - works if there are no spaces between the name &
numbers
=LEFT(A1,LEN(A1) - 9) - works if there is one space between the name &
numbers
 
J

JulieD

Hi

in an adjacent column you can use
=LEFT(A1 - LEN(A1)-3)

which will give you the contents of the cell minus the last three characters
 
H

Homer

Thanks Duke
Nice one!

Duke Carey said:
In an empty column, enter this formula (assuming your names are in column A
and all the numbers are 8 characters long)

=LEFT(A1,LEN(A1) - 8) - works if there are no spaces between the name &
numbers
=LEFT(A1,LEN(A1) - 9) - works if there is one space between the name &
numbers
Click to expand...
 
K

krissmith7

Hi,
I have a column of zip codes. Some zips have just the first five digits,
some have the first five plus a hyphen and four more digits. I only want the
first five. Now when I used that first formula....

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

....it worked great for the zips with too many numbers on the end, but it
didn't duplicate the ones that only had five, it just left it blank. How do
I get it to duplicate the first five AND delete the ones with too many
digits, so I'll be able to just delete the old column.

Thanks,
Kristin
 
G

Gord Dibben

Kristin

Rather than a formula select the column and Data>Text to Columns>Delimited by
- then Next

Select the right-hand column in the dialog and choose "do not import(skip)"


Gord Dibben Excel MVP
 
K

krissmith7

Thanks for the help!

I'm not at work at the moment to test out the two things mentioned, but I
will tomorrow.....after I posted earlier I kept messing around with it, and I
found out a formula that works, it went something like...

=LEFT(A1,LEN(A1=5))

That worked too.
Thanks again!
 
Top