Truncate number in cell

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,

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Chris Hedlund" <[email protected]> wrote in message
news:[email protected]...
> 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,
>
>

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,


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Chris Hedlund" <[email protected]> wrote in message
> news:[email protected]...
>> 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,
>>
>>
>

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Chris Hedlund" <[email protected]> wrote in message
news:[email protected]...
> 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,
>
>
> "Ragdyer" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Chris Hedlund" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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,
> >>
> >>
> >
>
>

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?


"Ragdyer" <[email protected]> wrote in message
news:%[email protected]...
> 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?
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Chris Hedlund" <[email protected]> wrote in message
> news:[email protected]...
>> 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,
>>
>>
>> "Ragdyer" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> > --
>> > HTH,
>> >
>> > RD
>> >
>>
>> --------------------------------------------------------------------------
> -
>> > Please keep all correspondence within the NewsGroup, so all may benefit
> !
>>
>> --------------------------------------------------------------------------
> -
>> > "Chris Hedlund" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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,
>> >>
>> >>
>> >
>>
>>
>

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!
=====================================================

"Chris Hedlund" <[email protected]> wrote in message
news:%[email protected]...
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?


"Ragdyer" <[email protected]> wrote in message
news:%[email protected]...
> 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?
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Chris Hedlund" <[email protected]> wrote in message
> news:[email protected]...
>> 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,
>>
>>
>> "Ragdyer" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> > --
>> > HTH,
>> >
>> > RD
>> >
>>
>> -------------------------------------------------------------------------
-
> -
>> > Please keep all correspondence within the NewsGroup, so all may benefit
> !
>>
>> -------------------------------------------------------------------------
-
> -
>> > "Chris Hedlund" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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,
>> >>
>> >>
>> >
>>
>>
>