I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

HTH
Jean-Guy

"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

OOPS..........should be 100 not 10.

"pinmaster" wrote:

Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

HTH
Jean-Guy

"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

Do that twice, or use 100 instead of 10?
--
David Biddulph

"pinmaster" wrote in message
...
Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I don't want to type the number
over
again.

Try this:

Enter 0.01 in an empty cell
Copy that cell: EditCopy
Select the range of cells that you want to convert
Then: EditPaste SpecialMultiplyOK

Biff

"Tom in Alaska" <Tom in wrote in message
...
I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I don't want to type the number over
again.

=a1/100
(if you typed the number in A1.)

You may want to experiment with:
tools|Options|Edit tab|Fixed decimals (2 decimal places)

But this will affect all your numeric entry--unless you type the decimal point.

Tom in Alaska wrote:

I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

--

Dave Peterson

Assuming that you want to change the origonal cells, enter 100 in an unused
cell and copy it. Then highlight the cell you want change an right-click
and select Paste Special Divide

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Tom in Alaska" <Tom in wrote in message
...
I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I don't want to type the number over
again.

hi tom in ALASKA,

try to display it only without altering the real value, then maybe like
this...
formatcellsnumberCustom
type this { ##"."#0} ----remove the brace

regards there,
--
*****
birds of the same feather flock together..



"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

format the field with 2 decimals

"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I don't want to type the number over
again.

I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...
format the field with 2 decimals

"Tom in Alaska" wrote:

I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I don't want to type the number
over
again.

What if the number isn't a number necessarily but just a string of numbers formatted as text?...more specifically:

123.1234.1234.1234.12345 (where 123 is the first segment containing three numbers and these numbers are not really 123 etc. I used "123" to indicate the number of digits in each segment, ie a product code.

But in excel, the segments appear together like this because they were pasted from the original source (to which we do not have access):

12312341234123412345

How can I insert "decimals" to the appropriate spots without changing the format? My guess is that it's some sort of macro I need to write/create but I have no clue where to begin. Making it display a certain way isn't enough - the contents of the cells actually need to be modified without the format being affected. Right now, the process of adding these decimals or segment separators is done manually, thus being a long and tedious process.

Any assistance is appreciated!



David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
07-Feb-07

I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...

Previous Posts In This Thread:

On Wednesday, February 07, 2007 1:37 PM
Tom in Alask wrote:

How do I insert a decimal into a fixed number?
I want to take a fixed number in a cell, like 84543421, and I want a formula
that will display it as 845434.21 but I do not want to type the number over
again.

On Wednesday, February 07, 2007 1:47 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

HTH
Jean-Guy

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 1:49 PM
T. Valko wrote:

How do I insert a decimal into a fixed number?
Try this:

Enter 0.01 in an empty cell
Copy that cell: EditCopy
Select the range of cells that you want to convert
Then: EditPaste SpecialMultiplyOK

Biff

On Wednesday, February 07, 2007 1:53 PM
Dave Peterson wrote:

=a1/100(if you typed the number in A1.
=a1/100
(if you typed the number in A1.)

You may want to experiment with:
tools|Options|Edit tab|Fixed decimals (2 decimal places)

But this will affect all your numeric entry--unless you type the decimal point.

Tom in Alaska wrote:

--

Dave Peterson

On Wednesday, February 07, 2007 1:55 PM
Sandy Mann wrote:

Assuming that you want to change the origonal cells, enter 100 in an unused
Assuming that you want to change the origonal cells, enter 100 in an unused
cell and copy it. Then highlight the cell you want change an right-click
and select Paste Special Divide

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Tom in Alaska" <Tom in wrote in message
...

On Wednesday, February 07, 2007 2:09 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
OOPS..........should be 100 not 10.

"pinmaster" wrote:

On Wednesday, February 07, 2007 2:34 PM
David Biddulph wrote:

Do that twice, or use 100 instead of 10?
Do that twice, or use 100 instead of 10?
--
David Biddulph

On Wednesday, February 07, 2007 4:56 PM
drille wrote:

hi tom in ALASKA,try to display it only without altering the real value, then
hi tom in ALASKA,

try to display it only without altering the real value, then maybe like
this...
formatcellsnumberCustom
type this { ##"."#0} ----remove the brace

regards there,
--
*****
birds of the same feather flock together..



"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:00 PM
Barr wrote:

How do I insert a decimal into a fixed number?
format the field with 2 decimals

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:12 PM
David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorials...nless-url.aspx

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gabrielle" wrote in message ...
What if the number isn't a number necessarily but just a string of numbers
formatted as text?...more specifically:

123.1234.1234.1234.12345 (where 123 is the first segment containing three
numbers and these numbers are not really 123 etc. I used "123" to indicate
the number of digits in each segment, ie a product code.

But in excel, the segments appear together like this because they were
pasted from the original source (to which we do not have access):

12312341234123412345

How can I insert "decimals" to the appropriate spots without changing the
format? My guess is that it's some sort of macro I need to write/create
but I have no clue where to begin. Making it display a certain way isn't
enough - the contents of the cells actually need to be modified without
the format being affected. Right now, the process of adding these decimals
or segment separators is done manually, thus being a long and tedious
process.

Any assistance is appreciated!



David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
07-Feb-07

I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...

Previous Posts In This Thread:

On Wednesday, February 07, 2007 1:37 PM
Tom in Alask wrote:

How do I insert a decimal into a fixed number?
I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I do not want to type the number
over
again.

On Wednesday, February 07, 2007 1:47 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

HTH
Jean-Guy

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 1:49 PM
T. Valko wrote:

How do I insert a decimal into a fixed number?
Try this:

Enter 0.01 in an empty cell
Copy that cell: EditCopy
Select the range of cells that you want to convert
Then: EditPaste SpecialMultiplyOK

Biff

On Wednesday, February 07, 2007 1:53 PM
Dave Peterson wrote:

=a1/100(if you typed the number in A1.
=a1/100
(if you typed the number in A1.)

You may want to experiment with:
tools|Options|Edit tab|Fixed decimals (2 decimal places)

But this will affect all your numeric entry--unless you type the decimal
point.

Tom in Alaska wrote:

--

Dave Peterson

On Wednesday, February 07, 2007 1:55 PM
Sandy Mann wrote:

Assuming that you want to change the origonal cells, enter 100 in an
unused
Assuming that you want to change the origonal cells, enter 100 in an
unused
cell and copy it. Then highlight the cell you want change an right-click
and select Paste Special Divide

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Tom in Alaska" <Tom in wrote in message
...

On Wednesday, February 07, 2007 2:09 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
OOPS..........should be 100 not 10.

"pinmaster" wrote:

On Wednesday, February 07, 2007 2:34 PM
David Biddulph wrote:

Do that twice, or use 100 instead of 10?
Do that twice, or use 100 instead of 10?
--
David Biddulph

On Wednesday, February 07, 2007 4:56 PM
drille wrote:

hi tom in ALASKA,try to display it only without altering the real value,
then
hi tom in ALASKA,

try to display it only without altering the real value, then maybe like
this...
formatcellsnumberCustom
type this { ##"."#0} ----remove the brace

regards there,
--
*****
birds of the same feather flock together..



"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:00 PM
Barr wrote:

How do I insert a decimal into a fixed number?
format the field with 2 decimals

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:12 PM
David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorials...nless-url.aspx

You could try this formula to recreate the original:
=left(a1,3)&"."&mid(a1,4,4)&"."&mid(a1,8,4)&"."&mi d(a1,12,4)&"."&right(a1,5)

Regards,
Fred

"gabrielle" wrote in message ...
What if the number isn't a number necessarily but just a string of numbers
formatted as text?...more specifically:

123.1234.1234.1234.12345 (where 123 is the first segment containing three
numbers and these numbers are not really 123 etc. I used "123" to indicate
the number of digits in each segment, ie a product code.

But in excel, the segments appear together like this because they were
pasted from the original source (to which we do not have access):

12312341234123412345

How can I insert "decimals" to the appropriate spots without changing the
format? My guess is that it's some sort of macro I need to write/create
but I have no clue where to begin. Making it display a certain way isn't
enough - the contents of the cells actually need to be modified without
the format being affected. Right now, the process of adding these decimals
or segment separators is done manually, thus being a long and tedious
process.

Any assistance is appreciated!



David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
07-Feb-07

I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...

Previous Posts In This Thread:

On Wednesday, February 07, 2007 1:37 PM
Tom in Alask wrote:

How do I insert a decimal into a fixed number?
I want to take a fixed number in a cell, like 84543421, and I want a
formula
that will display it as 845434.21 but I do not want to type the number
over
again.

On Wednesday, February 07, 2007 1:47 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
Hi,

One way: Type the number 10 in a blank cell then copy it, next select your
data and do a paste special/Divide.

HTH
Jean-Guy

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 1:49 PM
T. Valko wrote:

How do I insert a decimal into a fixed number?
Try this:

Enter 0.01 in an empty cell
Copy that cell: EditCopy
Select the range of cells that you want to convert
Then: EditPaste SpecialMultiplyOK

Biff

On Wednesday, February 07, 2007 1:53 PM
Dave Peterson wrote:

=a1/100(if you typed the number in A1.
=a1/100
(if you typed the number in A1.)

You may want to experiment with:
tools|Options|Edit tab|Fixed decimals (2 decimal places)

But this will affect all your numeric entry--unless you type the decimal
point.

Tom in Alaska wrote:

--

Dave Peterson

On Wednesday, February 07, 2007 1:55 PM
Sandy Mann wrote:

Assuming that you want to change the origonal cells, enter 100 in an
unused
Assuming that you want to change the origonal cells, enter 100 in an
unused
cell and copy it. Then highlight the cell you want change an right-click
and select Paste Special Divide

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Tom in Alaska" <Tom in wrote in message
...

On Wednesday, February 07, 2007 2:09 PM
pinmaste wrote:

How do I insert a decimal into a fixed number?
OOPS..........should be 100 not 10.

"pinmaster" wrote:

On Wednesday, February 07, 2007 2:34 PM
David Biddulph wrote:

Do that twice, or use 100 instead of 10?
Do that twice, or use 100 instead of 10?
--
David Biddulph

On Wednesday, February 07, 2007 4:56 PM
drille wrote:

hi tom in ALASKA,try to display it only without altering the real value,
then
hi tom in ALASKA,

try to display it only without altering the real value, then maybe like
this...
formatcellsnumberCustom
type this { ##"."#0} ----remove the brace

regards there,
--
*****
birds of the same feather flock together..



"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:00 PM
Barr wrote:

How do I insert a decimal into a fixed number?
format the field with 2 decimals

"Tom in Alaska" wrote:

On Wednesday, February 07, 2007 5:12 PM
David Biddulph wrote:

I don't know how your Excel works, Barry, but for most folk if they have
I don't know how your Excel works, Barry, but for most folk if they have
84543421 in a cell and format the cell with 2 decimals, it displays
84543421.00, not the 845434.21 which the OP requested.
--
David Biddulph

"Barry" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorials...nless-url.aspx