Truncate number in cell

C

Chris Hedlund

I want to input a number into a cell
say for example: 1,515,199,455
and have Excel automatically truncate the last 6 digits off
so the number ends up: 1,515

Can this be done?

Thanks,
 
R

Ragdyer

The procedure depends on exactly what you want to do with that "1,515",
And if you want it to *really* be 1,515, or just *display* as 1,515, where
the original number is still the original value.

Also, will you want to revise other numbers with this same procedure?
If so, will their "size" be comparable to your example?

Just a couple of ways:

=--LEFT(A1,5)
=TEXT(A1,"#,###,,")

If these aren't good enough, post back with more details.
 
C

Chris Hedlund

thanks - both those solutions work and are pretty cool - but I was thinking
more along the lines of just applying a format to a cell so that when I type
the numbers in the cells down the list, it chops off the last 6 digits.

I'm gonna think more about your solution thou - might work if I change the
worksheet around.

Thanks,
 
R

Ragdyer

You still haven't mentioned exactly what you're looking for.

Do you want the cell to retain the full value of the entered number?
You're implying that by using the word "format", which means just changing
the way a value is displayed, without actually changing the value.

If that's the case, you could apply a custom format, just as I posted with
the TEXT() function:

Select the cells to format, then,
<Format> <Cells> <Number> tab,
And click on "Custom".

In the "Type" box, enter:
#,###,,
Then <OK>.

You've now formatted the selected cells, so that an entry of any number will
be automatically displayed in relation to one million.
Anything under a million will return an empty cell.

Is this what you're looking for?
 
C

Chris Hedlund

ooooohhhh - I didn't catch that - I see now.

No - I don't want to retain the original number - I just want the number
after it's truncated.

One thing I noticed is that the TEXT funcition aligns the number to the left
of the cell instead of the right. Will that affect summing, etc?
 
R

RagDyeR

The Text() function does change the returned data to text, which the Sum()
function will *not* total.

That can be easily remedied by simply adding a double unary to the front of
the function, like so:

=--TEXT(A1,"#,###,,")

However, this does *not* return exactly what you're asking for.

If applied to 1,515,500,455
the return would be 1,516

Which is not simply truncating the number.

Since you're starting with real numbers, try this:

=INT(A1/1000000)

Then just format the column to insert the commas.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

ooooohhhh - I didn't catch that - I see now.

No - I don't want to retain the original number - I just want the number
after it's truncated.

One thing I noticed is that the TEXT funcition aligns the number to the left
of the cell instead of the right. Will that affect summing, etc?
 
Joined
Jun 14, 2006
Messages
27
Reaction score
0
You can do it in an adjacent column with the formula =int(left(A1,4)) - this assumes that the long number is in cell A1


You then have to format it comma style and reduce the decimal points to 0.

It also does not 'roundup' the number if this is important

Regards

Zoddy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top