tricky formatting Q (perhaps not so tricky)

.......this has been driving my wife and i crazy......but it's probably easy 
stuff for some of you.

we have a list of currency amounts....$23.61, $54.67, $110.00, $54.30

in place of those numbers, what we would like are:

00002361, 00005467, 00011000, 00005430

so basically wanting to have an eight digit number, with zeros preceeding 
the point that the number is displayed such that it is the last X number of 
places to complete the 8 digit requirement)

is there a way to first do a check for the length of the currency 
number....length as in number of places......and then return the zeros to 
result in the eight digit number.

i hope i am explaining that in a non confusing manner.

thanks in advance...my wife is convinced that it can't be done.

jlally@ctmbd.com 


0
jlally (3)
3/23/2007 10:26:08 PM
excel 39879 articles. 2 followers. Follow

3 Replies
523 Views

Similar Articles

[PageSpeed] 44

Hi Jim

with the first value in A1, and the rest going down the column.
Select cell B1, Format>Cells>Number>Custom>00000000
Then enter formula
=A1*100
Copy down as far as required.

-- 
Regards

Roger Govier


"Jim Lally" <jlally@ctmbd.com> wrote in message 
news:1uYMh.98$ha4.35@newsfe12.lga...
> ......this has been driving my wife and i crazy......but it's probably 
> easy stuff for some of you.
>
> we have a list of currency amounts....$23.61, $54.67, $110.00, $54.30
>
> in place of those numbers, what we would like are:
>
> 00002361, 00005467, 00011000, 00005430
>
> so basically wanting to have an eight digit number, with zeros 
> preceeding the point that the number is displayed such that it is the 
> last X number of places to complete the 8 digit requirement)
>
> is there a way to first do a check for the length of the currency 
> number....length as in number of places......and then return the zeros 
> to result in the eight digit number.
>
> i hope i am explaining that in a non confusing manner.
>
> thanks in advance...my wife is convinced that it can't be done.
>
> jlally@ctmbd.com
> 


0
roger5293 (1125)
3/23/2007 10:31:55 PM
Step1
=A1*100

Step2
menu: Format>Cell..., tab: Number, category: 'Custom', type: 00000000

-- 
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


"Jim Lally" <jlally@ctmbd.com> wrote in message news:1uYMh.98$ha4.35@newsfe12.lga...
> ......this has been driving my wife and i crazy......but it's probably easy 
> stuff for some of you.
> 
> we have a list of currency amounts....$23.61, $54.67, $110.00, $54.30
> 
> in place of those numbers, what we would like are:
> 
> 00002361, 00005467, 00011000, 00005430
> 
> so basically wanting to have an eight digit number, with zeros preceeding 
> the point that the number is displayed such that it is the last X number of 
> places to complete the 8 digit requirement)
> 
> is there a way to first do a check for the length of the currency 
> number....length as in number of places......and then return the zeros to 
> result in the eight digit number.
> 
> i hope i am explaining that in a non confusing manner.
> 
> thanks in advance...my wife is convinced that it can't be done.
> 
> jlally@ctmbd.com 
> 
>
0
3/23/2007 10:32:21 PM
Jim,

The amount in A2 and then

=TEXT(100*A2,"00000000")

produces realy text strings of 8 digits.
By using format options instead, you get only the look of 8 digits.
Its up to you what it has to be.

Jan


0
jg8091 (25)
3/23/2007 10:41:42 PM
Reply:

Similar Artilces:

Excel Date Formatting
Hi, In Excel the regional settings are set to short date format. However, if you try and format cells individually with a date, the date changes, but when the spreadsheet is closed and the reopened the formatting reverts to the short date format! We are using Excel 2000 with sp3 on XP (If thats any help!) If anyone has any ideas why this is occuring i'd love to know! Cheers, Hayden Are you saying that the same person on the same pc opens the file and the date format has changed? If it's another user (or another pc), then it could be the way excel picks up the date format if ...

Chart Formatting
Is it possible to change the color of a bar chart depending on the value of a cell? I want the bar graph to show 2 colors BUT would like for the colors on the Provider bar to show Green above the 60% and show Red below 60%. Member's bar to show Green above 80% and show Red below 80%. Check this out: http://peltiertech.com/Excel/Charts/ConditionalChart1.html -- Best Regards, Luke M "jlo" <jlo@discussions.microsoft.com> wrote in message news:D9CABBE1-828D-43D6-BD8F-276D27C35204@microsoft.com... > Is it possible to change the color of a bar chart...

Trailing Space / Format Question
Hey gang I have a spreadsheet in Excel 2003 that has values imported from a Crystal report. All the numbers have a trailing space, so I can not format them to show dollar signs, decimals, comma seperators, etc... I have tried to use the TRIM function, then copy/paste>Values only, but the values only paste gives me back the trailing space. Any ideas? Try this formula in a help column =--TRIM(SUBSTITUTE(A1,CHAR(160)," ")) copy and paste special as values and then use number formatting Regards, Peo Sjoblom "hagen" wrote: > Hey gang > > I have ...

Time formatting & calculating
Below is the chain of questions that I still need answers for. I am new at the time formatting, ascending or descending times, converting time and how to identify shift start and end so excel can read it. As you will read I NEED HELP. Our employees that are called for emergency work on designated holidays earn two types of reimbursable time. If they work during what would have been their normal shift they receive "H" leave for those hours. If they work outside (whether called in prior or extend beyond or totally outside)what would have been their normal shift, they earn &...

Help with formatting text in a textbox
Hi All, I have been trying everything I can think of, and still not getting the results I'm after. I am trying to summarize an order in a textbox, by building a string and assigning it to the caption property of the text box. I create a recordset based on what's in the table, then loop through it and build my string(s). I want to display everything nicely, so I am trying to space each field so that I can put in a header row of labels, and then have each record show nicely spaced below. I am now finding that even when I take into account the length of the data inthe field, I still ca...

Preserve Pivot Format
I'm currently testing out Office 2007 for my company (we currently use 2003) and I've run into a couple of questions regarding pivot table formatting. 1. In 2003, when I told a pivot table to "preserve format," it actually worked. For example, I have a page field in which the text gets very long. I'd like to have it "taller" and with wrapped text. In 2003, I changed that field to "all", set my formatting, and refreshed the pivot - worked great, the cell stayed as formatted. In 2007, I have "Preserve cell formatting on update" checked. I rep...

Is it possible to search for a particular format in cells?
I am in the process of compiling together lots of analytical data concering metals concentrations in sediment samples that we send out to various labs for analysis. For instance, sometimes lab A lists the percent error of silver as 5.6% by using percent formatting on a cell with the value .056. Lab B will list the error as 5.6 with general formatting (which I prefer). The problem with the first situation is if the formatting of the cell is ever lost (ie: 5.6% becomes .056). I eventually compile all of the results from the various labs onto one giant spreadsheet and in the proces...

Conditional Formatting #31
Hello I would like to use a Check for Underline in conditional formatting, is it possible andwhat is the syntax for the command Robin AFAIK, there's no way to check for whether an underline has been applied without using VBA. You could use this UDF: Public Function CheckForUnderline(Optional rCell As Range) As Boolean Application.Volatile If rCell Is Nothing Then Set rCell = Application.Caller(1) CheckForUnderline = Not _ rCell.Font.Underline = xlUnderlineStyleNone End Function Then in your CF: Formula is =CheckForUnderline(A1) Not...

Formatting Phone Numbers in a Merged Cells...
If I want to merge a "Company Name", "Phone Number" and a "Fax" number into 1 cell (for use in a drop down box), is there a way to maintain the 10 digit format for the phone/fax numbers? The phone/fax #'s appear as a string of 10 digits when they are merged, and are hard to read. Currently using a formula like this: =A3&" Phone: "&B3&" Fax: "&C3 I am tired of trying to get it to work, and can't seem to find the answer here. Thank you in advance! One way: =A3 & TEXT(B3,""" Phone:...

Cell Number Format
Hi, when i type for example this any number with more than 15 digits, the first 15 digits appear like i wrote it but the rest of them are changed by zeros. EX: i write this 123456789123456789 and the number that is kept is 123456789123456000 Does anyone experienced this and knows how to resolve it. Thanks, Ricardo As you can verify in XL Help ("Specifications"), XL is limited to 15 decimal digits of precision for numbers. If you're entering, say, a credit card number, which doesn't require any math to be performed on it, you can preformat the cell as Text or prefix ...

Format a cell to display decimal hours.
How do I format a cell to display decimal hours? Instead of displaying 3:45 (for 3 hours, 45 minutes), which is one of the standard formats, I'd like to display 3.75 (hours). h.hh doesn't do it. The above is formatting for an elapsed time calculation (finish time minus start time). (On a "time card") Excel 2000. Will a later Excel do it for me? Some good source for a parsing function? Any good reference? Thanks, Fred Holmes Fred, Format the cell in #.## format and multiply your result by 24. http://HelpExcel.com "Fred Holmes" wrote: > How do I forma...

Phone Format
One of my clients is using the letter writting assistant in GP 8, the problem is that when he tries to use LWA(Letter Writting Assistant), having Phone number field is what appears to be in US format, which is really annoying, i was wondering if anyone might have come accross a similar issue. Having said that I am unable to sell modifier to him because of his budget contraints and makes it really useless from his perspective to spend about 2000 odd quid over something this small. Any ideas on how to go about it without using Modifier. Make sure you have v8.0 Service Pack 4 installed...

How do I format a number in a currency format in word
I am doing a mail merge using number fields from an excel pread sheet. How can I format them like currency i.e. $1,287.22. Can the execl format be passed to word? Can this be done or must I use the field edit and use the \# switch ? Convert it to a Text field is one one way. F1: =TEXT(E1,"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)") http://www.mvps.org/dmcritchie/excel/mailmerg.htm If you are more familiar with MS Word than with Excel Control Word from Excel [-- http://www.erlandsendata.no/english/index.php?d=envbaolecontrolword --] Send...

Table formatting lost
I receive emails that started off in Excel as a short table. The sender then cuts and pastes into Outlook. When the message arrives it has lost all formatting for the table. Is there any solution apart from sennding as an Excel attachment? Do the copy as a Picture and then paste in Outlook. -- Gary''s Student - gsnu2007k "him@privacy.net" wrote: > I receive emails that started off in Excel as a short table. The > sender then cuts and pastes into Outlook. When the message arrives it > has lost all formatting for the table. Is there any solution apart > from se...

xml document format for excel
Does anyone know where I can get a copy of xml documentation for excel. I need to be able to produce xml files that are readable by excel and would like a doc set that defines all of the xml tags/params for excel If I understand you correctly, I think this is what you're looking for: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "Paul" <anonymous@discussions.microsoft.com> wrote in message news:093801c3a301$9483db70$a001280a@phx.gbl......

Passing formatting variables when creating XLS from web
Hi, I'm currently allowing my users to export content from an online DB accessible from our Intranet into excel. I am wanting to see if it's possible to dictate to Excel by passing a variable to define the page scaling so the resulting xls file is formatted to fit the printed content a single page? Any answers to this would be appreciated, as i've spent a bit of time searching around this morning with no luck. hi, not sure how you are downloading but code might do what you want. With Worksheets("Sheet1").PageSetup .Zoom = False .FitToPagesTall = 1 .FitToP...

onSysCommand
Hi, Hi have few view, and I want to navigate through all of them. I want also that their status(RESOTRE,MAXIMIZE,MINIMIZE) will always be remained. For now, to do that I send I call my OnSysCommmand within my OnMDIActivate. The problem is that I C the window flickers if I move from a Maximize window to a restore one. How do I prevent it? 10x, Janiv. Janiv Ratson wrote: > Hi, > Hi have few view, and I want to navigate through all of them. > I want also that their status(RESOTRE,MAXIMIZE,MINIMIZE) will always be > remained. > For now, to do that I send I call my OnSysCommmand...

Q: How different colum-widths?
Hello NG, i am working on a word document, wich contains some excel sheets as inserted objects. now i want to merge some of these excelsheets. unfortunately they have different coloum- widths. is it possible to merge them and say to ecxel, that from here and further down to use different coloum- widths, then in the upper part of the sheet? thanx, Tom. -- _______________________ http://tom.lautenbacher.biz _______________________ Excel doesn't support different columnwidths per row. You might be able simulate it by merging cells together (format|cells|alignment tab). But merging ...

cell format default
I am using Excel 2003 and the default cell format on every Excel file is General of course. I want to change it to a different format as defualt whenever I open Excel. Is there a way to do this? I cannot find this anywhere Select all cells, change it to the format you want. Do this for each sheet. Save the workbook as Book.Xlt and give it the file type of Template (do this part first), and save it in the XLSTART directory - usually in C:\Program Files\Microsoft Office\OFFICE11\XLSTART Close the workbook. From now on, every workbook will be a copy of this. If you ever want to revert to n...

Wrap text limits in Excel 2003 cell formatting
We have an Excel document which needs to support rather lengthy text entries. We formatted the cell to wrap the text, but at a certain point, it stops wrapping. In the Knowledge base, I've found basic information regarding formatting cells and it included the following statement. " Select Wrap Text to wrap the text in the selected cell. The number of wrapped lines depends on the width of the column and the length of the cell contents." So I guess my question is does anyone know how to control the number of wrapped lines OR is there a fixed limit to the text wrapping i...

Conditional Number Formatting
I have a sheet where a number of cells require data input. Dependin where in the world the user of the sheet is, and depending on th preferences of the company the user is working for, there are a numbe of different units (i.e. "Ft" or "m" for length) that the various dat numbers represent. I've created lists of all possible units for each data type and create dropdown boxes to select the appropriate units. What I would like to occur is, depending upon the value selected in th drop-down list box, the data cell will apply a specific Custom Numbe Format. For example, t...

Q: Exchange 5.5 logging options?
What is the minimum logging options in Exchange 5.5 to be able to log mail traffic with the following information: Sender Address Destination Address Subject Line Date and Time And maybe message size This for, in the first case, SMTP mail but if it�s possible, even for internal mails. /Peter Message Tracking logs will give you this data (except subject, which is useless anyway). You have to enable it on MTA Site Configuration Information Store Site Configuration and on each connector. If you insist on subject you will have setup message journaling (see release notes for your current Exc...

Conditional Formatting #46
In Excel 97, is it possible to have more than 4 conditional formats? Maybe up to 6 or 7. I am looking at changing the color of a row (say columns A through H) based on the value in column H and is a date format. An example would be: - If the date in H1 is past today, cells A1 through H1 are Red. - If the date in H1 is between tomarrow and 7 days from today, cells A1 through H1 are Blue. - If the date in H1 is between 8 days from today and 14 days from today, cells A1 through H1 are Yellow. - If the date in H1 is between 15 days from today and 21 days from today, cells A1 through H1 are...

custom number format #10
Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. If a format contains a zero (0) or number sign(# ) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or "e+" places a minus sign by negative exp...

Change date format on report
My date displays as mddyy in the table but on the report I'd like it to display as mm/dd/yyyy, how would I code that to have that field always display in mm/dd/yyyy format. I'm thinking this is very simple but I just can't get it to work for me. Thank so much for your help. On May 2, 1:50 pm, SITCFanTN <SITCFa...@discussions.microsoft.com> wrote: > My date displays as mddyy in the table but on the report I'd like it to > display as mm/dd/yyyy, how would I code that to have that field always > display in mm/dd/yyyy format. I'm thinking this is very simple...