Formatting Cells 6

[Stumpy]

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

 

[JAE]

I tried the same on my Excel and it worked fine (Excel 2000)

Are you sure that you are working with the same version?

 

[austim]

Hi Stumpy,

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?

 

[Stumpy]

Thanks JAE and Austim,
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

 

[tigrek]

I would just delete the messed-up cells and start again.
Saves lots of time.

 

[swertel]

On a similar note of tigrek's "delete cell," you may prefer to Clear Contents. That way the remaining cells won't shift so you don't have to worry about the data surrounding the bad cell. Clearing contents is supposed to clear all values, formula, and formatting to a cell... in essence as if the cell were from a brand new worksheet. You can then enter the data yourself and format it as necessary.
--Scott Wertel
scottw@interfaceforce.com

http://www.interfaceforce.com

 

[IJR]

Swertel

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<> &quot;0&quot;&quot;&quot;feet&quot;&quot; then
selection.numerformat=&quot;0&quot;&quot;&quot;feet&quot;&quot;
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

 

[IJR]

Well guys

****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=&quot;&quot;&quot;feet&quot;&quot;0.00&quot;
end sub

Thanks Swertel for the subject

IJR

 

[swertel]

Thanks for all the congrats, but I see &quot;Stumpy&quot; as the first name on the list.

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&quot; ft&quot;;&quot;-&quot;#,##0.000&quot; ft&quot;;0&quot; ft&quot;;@&quot; ft&quot;

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

http://www.interfaceforce.com

 

[DeeW]

I've been going through your excel formatting & it's been pretty helpful. I'm querying data from CAD & pulling it straight in to excel to do my calcs--no problem--so far. The problem is that my cad files need to be in architectural units (EX. 3'-6 1/4&quot and after I'm done I need to show the final numbers on the drawings and yes, they want them back in architectural units) Right now, to do the numbers, I have to either switch my units in cad and do my query to get the data. Do the calcs put the totals back in cad and reconvert them--Total pain. Or Reconvert them in excel (Just as big of a pain) Is there anyway to set up a complete architectural format in excel (either in the format or through VBA)? Oh yeah, using Excel 2000 & Autocad 2000.

Thanks
Deew

 

[austim]

This is in the nature of a &quot;Believe It Or Not&quot; solution to Stumpy's original problem in applying his custom formats to some intractable 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.

 

[Mala]

Hello Stumpy
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(&quot;-&quot;,A1)+1,FIND(CHAR(34),A1)-FIND(&quot;-&quot;,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)&&quot;'-&quot;&INT((B1-INT(B1))*12)&IF((B1*12-INT(B1*12))>0,TEXT((B1*12-INT(B1*12)),&quot;#??/??&quot,&quot;&quot&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

 

'Assign this macro to any key like Ctrl+Shift+T
Sub cellform()
ActiveCell.Select
Selection.NumberFormat = &quot;0.00&quot;
ActiveCell.Value = ActiveCell.Value & &quot; feet&quot;
End Sub

 

[Mala]

Won't that make it a text (string)?

 

[swertel]

No, because the cell contents are still the particular value that you input.

Cell formatting allows any type of &quot;string&quot; 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 &quot;format cells...&quot; You have the options of &quot;general, number, text, date, etc. and custom.&quot; Adding a &quot;feet&quot; 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. &quot;Feet&quot; does not show up and is not included in any equation that uses that cell.

--Scott

 

[IJR]

Swertel
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 & &quot;feet&quot;

which simply means convert whatever is there into a string.

You were probably talking about &quot;custom formatting&quot;, which is not included in his code.

Respects
IJR

 

[IJR]

But I must add:

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

 

[Mala]

IJR, you ARE refreshingly funny at times - but your point is very well made - sometimes there's considerable room for improvement in the recorded code XL tends to spit out.

 

[swertel]

IJR:

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 &quot;&&quot; symbol, which concatinates the value and therefore makes it a string. It doesn't just apply formatting.

Thanks for the clarification.

--Scott