Formatting Cells
Formatting Cells
(OP)
I like to include the "units" within the cell with the number. To do this, I point to the cell, right click, "format cells ...", number tab, "custom" at the bottom, then place the following in "type" window:
0.0" feet"
Any number placed in the cell will be rounded to a single digit past the decimal point followed by the units, " feet".
By placing the units within the same cell as the number, I save a column and my spreadsheet is more compact. Looks cleaner, too.
When attempting to accomplish this in someone else's spreadsheet, I've found cells that refuse to cooperate. Nothing seems to happen when I format the cell. The sheet is not protected, nor is the cell.
Any ideas?
JPRiley
0.0" feet"
Any number placed in the cell will be rounded to a single digit past the decimal point followed by the units, " feet".
By placing the units within the same cell as the number, I save a column and my spreadsheet is more compact. Looks cleaner, too.
When attempting to accomplish this in someone else's spreadsheet, I've found cells that refuse to cooperate. Nothing seems to happen when I format the cell. The sheet is not protected, nor is the cell.
Any ideas?
JPRiley





RE: Formatting Cells
Are you sure that you are working with the same version?
RE: Formatting Cells
That looks like a very clever trick, which works for me on a new sheet in Excel 7.0.
It sounds as if you only hit a problem in particular cells, not simply all cells in someone elses sheet. If that is so, presumably it must be more subtle than a simple matter of different software versions.
Can you give us a few more clues? eg, can you alter the contents of your problem cells even if you cannot reformat them?
RE: Formatting Cells
I don't think it is related to mismatched versions of Excel. It is only a few cells that are not behaving. If you'd like, I can send you the file. Send me a private message at jpriley485@yahoo.com
RE: Formatting Cells
Saves lots of time.
RE: Formatting Cells
--Scott Wertel
scottw@interfaceforce.com
www.interfaceforce.com
RE: Formatting Cells
That custom formating you use is cool. Do you want to give me a line of code so that I can write a small macro in VB and invoke it with a shortcut key
I tried sub addfeet()
activecell.select
if selection.numberFormat<> "0"""feet"" then
selection.numerformat="0"""feet""
endif
endsub
assigned the macro to Ctr+t, aiming to be able to get rounded feet whenever I press Ctrl+t on a cell containing a number.
But it didnt work
Thanks all guys, for being around here
RE: Formatting Cells
****Swertel: I tried using your custom format on several machines in the office. Only once did I get an error WHILE DEFINING THE FORMAT in Format-cells-Custom dialog. The error was related to too much custom formats applied. I erased a couple and got my new format squeezed in. So could it be that you have more than enough custom formats in the dialog?
*****This one refers to my thread above. I dont really have to write code. One could simply record the macro the usual way. The code would simply read
sub applyfeet()
selection.numberformat="""feet""0.00"
end sub
Thanks Swertel for the subject
IJR
RE: Formatting Cells
I just created a macro that formats cells to what I typically use and assigned the macro to a hotkey. Then a select the cell(s) and hit the kotkey.
I have a template saved with the typical cell formats that I use, so I don't have to recreate them in every new workbook. The macro I record just has:
Right-click on the cell
Format Cells....
Select Custom and the custom format
Click OK
I have never got the too many custom formats applied error. My custom formats look something like this
#,##0.000" ft";"-"#,##0.000" ft";0" ft";@" ft"
positive value format;negative value format;zero format;text format
Applying this format yields the following:
Before format After format
.126879 0.127 ft
-.126879 -0.127 ft
0.000 0 ft
text text ft (makes no sense, just here for example)
12589.126879 12,589.127 ft
--Scott Wertel
scottw@interfaceforce.com
www.interfaceforce.com
RE: Formatting Cells
Thanks
Deew
RE: Formatting Cells
Stumpy was good enough to e-mail me a copy of the spreadsheet which was giving him troubles.
In one particular group of six cells, all with VLOOKUP functions referring to the same line in a data table, five cells could be formatted, but one defied any attempt at re-formatting. What ever method I tried, and whatever format I sought to apply , whether I used Excel 2000 or version 7.0, ... no result at all.
I deleted the cell contents and replaced them; still no progress.
Since I could think of nothing else to do, I then opened the data table, deleted the contents of the one cell that was being copied to the main page, went back to the main page, et voila .. the unformattable cell had reformatted as we wanted!
None of that makes any sense to me, but could be of some help to anyone who may come across a similar problem in the future.
RE: Formatting Cells
I've gone through all the responses (some very insightful) to your problem – here's my bit :
An applied number format does not 'round' the value to the specified no. of decimal places – it only appears rounded. Its simple to check this out – just try summing two identical columns of formatted and unformatted values. To force the accuracy of values to the accuracy displayed you have to do – Tools>Options>Calculation and in the 'Workbook Options' section select the 'Precision as displayed' checkbox.
For Swertel: Clear contents does not clear the formatting, just the values. To clear everything you do Edit>Clear>All.
For DeeW: The Architectural units of linear measurement does not lend itself to easy number formatting. However, here's something I could produce after half an hour of sweat.
I've created two formulas, one to convert architectural measurement to decimal format and the other to convert dec format back to arch. format.
Suppose cell A1 contains the value imported from AutoCad.
In the cell where u want to convert this value to decimals enter the formula:
=LEFT(A1,FIND(CHAR(39),A1)-1)+(MID(A1,FIND("-",A1)+1,FIND(CHAR(34),A1)-FIND("-",A1)-1))/12
This will convert the imported string (arch. format) to decimals
Let's say you entered this formula in cell B1
To convert the value in B1 back to arch. format, enter in cell C1:
=INT(B1)&"'-"&INT((B1-INT(B1))*12)&IF((B1*12-INT(B1*12))>0,TEXT((B1*12-INT(B1*12)),"#??/??"),"")&CHAR(34)
and you get back the string in architectural format.
I suggest u test these out with a variety of possible arch. format values
For converting a series of values simply copy the formulas in the appropriate ranges.
Note that the arch. format values are not numbers - they are strings.
Normally u would convert imported values to dec format, do calculations in XL on them and then convert the results obtained back to arch format using the second formula. The converted results could then be exported back to AutoCad.
On could also create user defined functions to do the above with the reference cell as argument.
Good luck!
Mala
RE: Formatting Cells
'Assign this macro to any key like Ctrl+Shift+T
Sub cellform()
ActiveCell.Select
Selection.NumberFormat = "0.00"
ActiveCell.Value = ActiveCell.Value & " feet"
End Sub
RE: Formatting Cells
RE: Formatting Cells
Cell formatting allows any type of "string" to be applied to the contents of the cell without changing the content type, i.e.: text string, integer, long, etc. Using the macro above is the same as right clicking on the cell and choosing "format cells..." You have the options of "general, number, text, date, etc. and custom." Adding a "feet" format is part of the custom format.
After you apply the format (in any of the ways shown above), click on the cell. Notice in the input line that only the value appears. "Feet" does not show up and is not included in any equation that uses that cell.
--Scott
RE: Formatting Cells
But the way Gps_2000 did it, it will change the value to a string(I have tested that as well). The reason is in his last line:
activecell.value=activecell.value & "feet"
which simply means convert whatever is there into a string.
You were probably talking about "custom formatting", which is not included in his code.
Respects
IJR
RE: Formatting Cells
Gps_2000's code is exactly what Excel writes for you when you manually record the macro, after which the macro works.
I have been through this before: excel writing code for you which if written directly buy you blows smoke at you
regs
IJr
RE: Formatting Cells
RE: Formatting Cells
You're right. I didn't test it and am not proficient at VBA or writing macros. I should have figured since the line in the macro contains the "&" symbol, which concatinates the value and therefore makes it a string. It doesn't just apply formatting.
Thanks for the clarification.
--Scott