Excel 2002 Formatting 2

[csmith10]

I'm importing dollar amounts from an ASCII file and need them to format a certain way.



Example:

This is how the numbers look after I import them into my spreadsheet.

1.23
12.23
123.45
1234.56


This is how I need the same numbers to look after I apply the proper formatting:

000000123
000001234
000012345
000123456

The two requirements are: [red][1][/red] All numbers must have the same number of digits (inserting the right amount of zeros), even though the initial imported values might vary; [red][2][/red] I have to keep the values to the right of the decimal point, but remove the decimal point itself.

Any help that can be offered is greatly appreciated.

 

[xlhelp]

Type 100 in a blank cell; highlight the cell with 100; go to Edit, Copy; highlight your data that you want to convert, go to Edit, Paste Special, select Multiply and click OK. Leaving the cells highlighted, go to Format, Cells, Number tab, select Custom, in the "Type" box on the right hand side enter 000000000

This assumes there are no negative numbers

 

[SkipVought]

CS,

Here's the problem. The wonderful posting from xlhelp is great for spreadsheet DISPLAY purposes.

BUT...

go and highlight the cell that you just formatted. All that you will see is

10000

WHERE ARE ALL THOSE LEADING ZEROES?????

Answer: The are for DISPLAY only.

You are dealing with numbers NOT numerals. What you want is 9 numerals.

So, here's the drill assuming that your raw decimal number is in A1...

=LEFT("00000000",9-LEN(A1*100))&A1*100

Hope this helps
Skip,
SkipAndMary1017@mindspring.com

 

[xlhelp]

Hey, Skip,

Good to see you back. Haven't seen your name pop up in a long time. How's the retirement?

 

[SkipVought]

Retired and still wanting to work. Aint't that a switch! Actually I need to bring in a few extra bucks each month.

But, YES, I am here for now Skip,
SkipAndMary1017@mindspring.com

 

[csmith10]

Thanks to both of you for the help...this gets me exactly what I'm wanting. I think I was overthinking the problem a little bit.

I do have one follow up question for Skip though. If I follow you correctly, you are saying that the formatting is only for the sake of the display. In what situation do you see that this would cause me a problem? In other words, what should be the determining factor between displaying the information as numerals vs numbers?

I set up the raw decimal in A1 and then ran both examples side-by-side. In colum B I only formatted the way xlhelp suggested, and in column C I did what you recommended. Then I saved the file as a Unicode.txt file so I could upload it to another program. In both cases, the preceding zeros were saved in the text document.

Thanks again for the input...this was very good information!

 

[SkipVought]

CS,
It only makes a difference if you need a certain number of characters in a particular field (column) and those characters have bounds. For an old COBOL programmer, if you are looking for a field of 9 digits, then something that's NOT a digit will cause an error. So you must fill with zeroes.

Digits are different than numeric values. the integer value, 1 is stored differently than the digit, 1, which has an ASCII value of 49. Skip,
SkipAndMary1017@mindspring.com

 

[csmith10]

That makes sense Skip, thanks for the clarification. Your help is very much appreciated (both of you). Thanks again!