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

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!

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



"homer" wrote:

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!

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"homer" wrote in message
...
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!

Thanks Duke
Nice one!

"Duke Carey" wrote:

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



"homer" wrote:

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!

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

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

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

What happens if you just take the leftmost 5? =LEFT(A1,5)


"krissmith7" wrote in message
...
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

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

Kristin

Rather than a formula select the column and DataText to ColumnsDelimited by
- then Next

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


Gord Dibben Excel MVP

On Wed, 14 Dec 2005 11:49:58 -0800, "krissmith7"
wrote:

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

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

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!



"Gord Dibben" wrote:

Kristin

Rather than a formula select the column and DataText to ColumnsDelimited by
- then Next

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


Gord Dibben Excel MVP

On Wed, 14 Dec 2005 11:49:58 -0800, "krissmith7"
wrote:

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