Custom number format
 

I am trying to format numbers without decimal points! Sounds easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because they are
importing into a COBOL system and any separator kills the process. I have
had no luck with this. Anyone have any ideas?

Thanks.

Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)



"E2engine" wrote in message
...
I am trying to format numbers without decimal points! Sounds easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because they are
importing into a COBOL system and any separator kills the process. I have
had no luck with this. Anyone have any ideas?

Thanks.

Hi
not possible with a format

--
Regards
Frank Kabel
Frankfurt, Germany

"E2engine" schrieb im Newsbeitrag
...
I am trying to format numbers without decimal points! Sounds easy,
but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because they
are
importing into a COBOL system and any separator kills the process. I
have
had no luck with this. Anyone have any ideas?

Thanks.

Where do you find "decimal" in the Edit Replace?
Thank you
-----Original Message-----
Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)



"E2engine" wrote in
message
news:64AF834D-F341-492D-B7DC-
...
I am trying to format numbers without decimal points!
Sounds easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is
because they are
importing into a COBOL system and any separator kills
the process. I have
had no luck with this. Anyone have any ideas?

Thanks.


.

N Harkawat was indicating that the

That will change the actual value of the cell, which is probably
not what the poster wants. You can't do what is asked with
formatting.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"N Harkawat" wrote in message
...
Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)



"E2engine" wrote in
message
...
I am trying to format numbers without decimal points! Sounds
easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because
they are
importing into a COBOL system and any separator kills the
process. I have
had no luck with this. Anyone have any ideas?

Thanks.

Well Chip, I can't say it is encouraging to hear from an Excel expert saying
it can't be done! So may I ask, if it cannot be done via formatting, is
there another approach I can take?

"Chip Pearson" wrote:

That will change the actual value of the cell, which is probably
not what the poster wants. You can't do what is asked with
formatting.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"N Harkawat" wrote in message
...
Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)



"E2engine" wrote in
message
...
I am trying to format numbers without decimal points! Sounds
easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because
they are
importing into a COBOL system and any separator kills the
process. I have
had no luck with this. Anyone have any ideas?

Thanks.

Thanks for your response. What you are suggesting would mean that the number
would first need to be formatted as text and then searched and replaced for
the period. It may work if I can figure out how to limit the true value of
the numbers to the exact number of characters. Don't forget a number
formated for 2 decimal places (which is the decimal formatted original value)
could actually be a longer string because of rounding. This would paste as
the full unrounded string. But I will investigate doing a Values paste on
this column and see what happens. Sounds like a Macro to me!

"N Harkawat" wrote:

Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)



"E2engine" wrote in message
...
I am trying to format numbers without decimal points! Sounds easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because they are
importing into a COBOL system and any separator kills the process. I have
had no luck with this. Anyone have any ideas?

Thanks.

Sure if this is only for use in the COBOL program multiply by 100 and
make formatting such as 00000
I think you want a fixed format.

What are you going to use for negative numbers, because that is
probably going to require more work on both sides though on your
part that might simply be a + or sign to the right of the number,
I think overpunches would be more complicated. Surprising that there
never was any provision for this in Excel.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"E2engine" wrote
Well Chip, I can't say it is encouraging to hear from an Excel expert saying
it can't be done! So may I ask, if it cannot be done via formatting, is
there another approach I can take?

"Chip Pearson" wrote:
That will change the actual value of the cell, which is probably
not what the poster wants. You can't do what is asked with
formatting.

"N Harkawat" wrote ..
Go to Edit -- replace
Search for .(decimal)
replace (leave that emplty)


"E2engine" wrote in
message
...
I am trying to format numbers without decimal points! Sounds
easy, but is
not. For instance:

1.72 should appear as 172
14.26 should appear as 1426

I am told the reason the customer needs this format is because
they are
importing into a COBOL system and any separator kills the
process. I have
had no luck with this. Anyone have any ideas?

You are not thinking in terms of what is needed. When you supply
the numbers they will be in the specific format required any rounding
if needed will be done on your side beforehand. What is wanted
is the number with a an assumed two decimals places. So you
multiply by 100 and format without any formatting characters on your side.
Everything should be in a specific position, because I doubt that COBOL
is going to handle CSV files unless the COBOL is on a PC.

"E2engine" wrote ...
Thanks for your response. What you are suggesting would mean that the number
would first need to be formatted as text and then searched and replaced for
the period.

David,
Thanks for your input. First, these are prices, so no negatives will be
involved, and second, stupid me tried your approach but as an addition, not
multiplication! Ah, the light is so blinding! Yikes! I will give it a
shot. And I understand the formatting issue and where it should happen.

Again, many thanks.

"David McRitchie" wrote:

You are not thinking in terms of what is needed. When you supply
the numbers they will be in the specific format required any rounding
if needed will be done on your side beforehand. What is wanted
is the number with a an assumed two decimals places. So you
multiply by 100 and format without any formatting characters on your side.
Everything should be in a specific position, because I doubt that COBOL
is going to handle CSV files unless the COBOL is on a PC.

"E2engine" wrote ...
Thanks for your response. What you are suggesting would mean that the number
would first need to be formatted as text and then searched and replaced for
the period.