 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Numeric Cell Format
I regularly copy a table from Excel to a text file that has fixed width I always have to adjust the numbers, however. Can I specify a numeric format so that 1,000.00 is displayed like this and that any preceding zeros would be spaces. eg, the following list of numbers would always line up to the right in a fixed width font 1,000.00 100.00 10.00 0.00 Eddie assuming the values you need to copy are in column A you could add another column with the following formula =REPT(" ",8-LEN(TEXT(A1,"0.00"))) & TEXT(A1,"#,##0.00") and copy down. you would then use the newly created column to copy and paste into your text doc. >I regularly copy a table from Excel to a text file that has fixed width > I always have to adjust the numbers, however. [quoted text clipped - 11 lines] > > Eddie small error, formula should have been: =REPT(" ",8-LEN(TEXT(A1,"#,##0.00"))) & TEXT(A1,"#,##0.00") Please let the group know if this did the trick > assuming the values you need to copy are in column A you could add > another column with the following formula [quoted text clipped - 20 lines] >> >> Eddie Thanks for the suggestion Gerry Unfortunately, the spreadsheet does a lot of "tricky" things and it isn't really possible to add another column without rewriting the macros! Is there a cell format that can do the business? Eddie > *From:* "GerryGerry" <Gerry@Gerry.anon> > *Date:* Tue, 18 Mar 2008 17:15:57 GMT [quoted text clipped - 29 lines] > >> > >> Eddie Perhaps a solution would be to insert an entire worksheet which effectively copies all the data from the original so for example if the original is named sheet1 you would insert a new sheet and put in the first cell (A1) : =sheet1!A1 then copy this both across and down as far as needed. You then can use the formula I gave in my first reply but on the newly added sheet (just precede the cell refs. in the formula with sheet1!) as follows: =REPT(" ",8-LEN(TEXT(sheet1!A1,"#,##0.00"))) & TEXT(sheet1!A1,"#,##0.00") Let us know if this was helpful > Thanks for the suggestion Gerry > [quoted text clipped - 38 lines] >> >> >> >> Eddie I've got my technical specialist looking at it and I'll report-back if we can fix it. I assume by the fact that you didn't specifically answer my question about cell formats, that there isn't a number format that can insert spaces. Eddie > *From:* "GerryGerry" <Gerry@Gerry.anon> > *Date:* Wed, 19 Mar 2008 09:27:38 GMT [quoted text clipped - 61 lines] > >> >> > >> >> Eddie We have discovered an easier way to do it on the lines of what I was suggesting: We use a cell format of ?,???.00 Eddie
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.