Remove all but the last seven digits in a cell

G

Gary Crisp

I have a Excel 2003 sheet that I download from a vendor and the column of
numbers that I need have more characters than I need. I am comparing the
column to another sheet so I need them to be uniform in length. Here is an
example: 075 - 9652582. I only need the 9652582 .
Thanks in advance
 
R

Roger Govier

Hi Gary

Try
Assuming the data is in column A, and there is nothing in columns to the
right
Mark column A>Data>Text to Columns>Delimited>Other >type in the box next to
other space hyphen " -" (without the quotes)>Finish
Column C will contain the numbers you want.
Columns A and B can be deleted if required.

Alternatively, in B1 enter
=RIGHT(A1,7) and copy down
Mark column B>Copy>Paste Special back over itself to "fix" the data
Delete column A
 
B

Bernard Liengme

Experiment with Data | Text to Columns. You will find it easy to separate
the two parts. Just use Delimited with -
best wishes
 
E

Eddie Ortiz

I was wondering if your got this to work already. One way you can try is this
insert this formula on a helper cell:

assuming the cell is A2

=left(A2,LEN(A2),-6)
 
R

Rick Rothstein \(MVP - VB\)

Two points...

1) You show an extra comma in your LEFT function...
you meant to post =LEFT(A2,LEN(A2)-6)

2) You didn't answer the question that was asked. The OP wants to keep the
last 6 characters, not remove them. The formula for that is =RIGHT(A2,6)

Did you post your message into the original thread? I ask because all I see
is your message... I see no other messages (not even the OP's original one;
although I do see your included copy of it) for the thread you have posted
to.

Rick
 
R

Roger Govier

Did you post your message into the original thread? I ask because all I
see is your message... I see no other messages (not even the OP's original
one; although I do see your included copy of it) for the thread you have
posted to.
Click to expand...

Rick, the reply from Eddie shows up in the thread posted originally
08/jan/2008 21:08 by Gary Crisp.
This shows up for me as part of the thread, as I had marked it (I had given
a response)
From time to time I do see totally (to me) disjointed responses just like
you appear to be seeing with this one.
I have no idea why this happens.

Thinking aloud about that a little further, I don't think I have seen that
problem since switching from OE6 to Windows Live Mail as my Newsreader.
 
R

Rick Rothstein \(MVP - VB\)

Thinking aloud about that a little further, I don't think I have seen that
problem since switching from OE6 to Windows Live Mail as my Newsreader.
Click to expand...

Is Windows **Live** Mail different than the Windows Mail that comes with
Vista, which is what I am using?

Rick
 
R

Rick Rothstein \(MVP - VB\)

Okay, thanks... that "marginal" difference may then account for why I saw
only one message originally and you saw the entire thread. Although in
thinking about it, I just recently switched from Comcast's cable service to
Verizon's FIOS service, so perhaps the removal of my old accounts, and the
establishment of my new ones, may be the underlying reason for the
difference.

Rick
 
Top