Custom number format

[Guest]

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]

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

 

[Frank Kabel]

Hi
not possible with a format

 

[Karen]

Where do you find "decimal" in the Edit > Replace?
Thank you

 

[JE McGimpsey]

N Harkawat was indicating that the

 

[Chip Pearson]

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

 

[Guest]

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?

 

[Guest]

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!

 

[David McRitchie]

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.

 

[David McRitchie]

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.

 

[Guest]

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.