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!

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

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

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

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