Excel question (cell formatting)

Excel has some pretty neat formatting features. One thing I like is the “custom number formatting,” wherein you can append custom text to a number.

For example, let’s say I have a column of voltages:

1.23 V
2.14 V
9.63 V

To do this, I would specify the following under “custom number formatting”:

0.00" V"

Pretty straight forward. Notice that everything (including V) has the same (default) font.

But what if I want the units to be a different font?

For example, lets say I want to make a column of resistance values (units = ohms), such as

2.14 [omega symbol]
5.12 [omega symbol]
5.63 [omega symbol]

I would like the numbers to be in the default font (Arial or whatever), but I would like to append an upper-case Omega symbol (in Symbol font) to the end.

Is it possible to do this using the “custom number formatting” feature?

I am pretty Excel savvy but there does not appear to be any way to do this. You can’t even format the special text separately from the value in a single cell, much less do it for all automatically.

Without knowing more about what you want to do it’s hard to suggest alternatives but you could always put your Omega in the next column to the right and make the column 1 or 2 characters wide. If you are using borders you just omit the border in between the value and the Omega cells and it looks the same as what you describe above, though not quite as cool as if it were automatic.

Well, you can change the font manually for each cell after you have entered the numbers and letters. This isn’t really convenient, but if you’re mostly worried about the spreadsheet’s usefulness to others, this might be the way to go. You might even experiment like this:

  1. enter just the numbers
  2. enter and format the character in the first cell.
  3. copy just the formatted character
  4. hit F2 and paste the character into the remaining cells.

You’re in for a lot of work.

That’s what I’ve done in the past – I created a column to the right of the numbers, formatted the column for “Symbol” font, and inserted the Ohms (Omega) symbol. Still, I would really like to know if there’s a way to format the number so that the Omega symbol is part of the number.

I did some searching in Excel’s “help” database. Apparently there’s a way to change the color by preceding the number format with the color name in brackets. For example, 0.00” V” will make the color blue. But no mention of how to change the font.

So I guess if I have a column of resistances, the Ohms symbol (Omega) MUST be in the next column to the right. There is apparently no way to make the Ohms symbol “integral” to the number.

What Crafter_Man is trying to do is use the cell format feature, and select the “custom” style (instead of General, Number, Currency, etc.). Using the “custom” style you can format the number any way you want plus add other text before & after it, just like the example in the OP. However, if you add text automatically via the format style, you will not actually see it if you try to edit the cell content; you will just see the number. Therefore you cannot use the custom style and format the number font different from the text.

Forgive me for being stupid here, but I inserted the ohm symbol into Word, copied it to the clipboard, opened the custom cell formatting tab in Excel and was able to quite easily paste the symbol into the custom formatting. Worked very nicely, and copied successfully into other cells.

Could you use a third cell containing something like “=CONCATENATE(A1,B1)” with the number in A1 and the symbol in B1? The symbol in B1 would be of the form “=CHAR(87)”
The B1 cell would have to be formated for the Symbol font.

I say again … just copy and paste the symbol into the custom cell formatting box. Honest. I’ve tried this in Windows 2000 and Windows 98 and haven’t had any problem.

It doesn’t work.

Do this:

Open Word.
Type the letter W.
Highlight the letter W.
Change font to Symbol. The W should turn into the Omega symbol.
Highlight the Omega symbol.
Copy to the clipboard.

So far so good, right?

Now highlight a cell in Excel.
Go to Format –> Cells… –> Number –> Custom
Type the following in the window: 0.00" @" ( @ is where you paste the Omega symbol from the clipboard)

Notice how it pastes a W and not the Omega symbol?

And if you hit the O.K. button , then type the number 5 in the cell, you’ll get:

5.00 W

You will not get

5.00 [Omega symbol]

Ah. Slightly different method.

Open Word. Don’t type W. Instead, select “Symbol” from the “Insert” menu. Look for the Omega symbol (it’s present in Times New Roman and in Arial, and presumably many others). Insert it, copy it, paste it into Excel.

Two different ways of inserting symbols. If you try to paste a symbol from one font into a cell formatted in another I suppose it’s defaulting to the original font for that cell. If you paste the symbol in the same font it should work.

I may be confused here. Happens all the time. I have formatted subscript characters in a cell with full-size text; I assumed that I could format fonts as well. When I’m back in the office I’ll try.

I forgot they expanded the character sets for fonts. So I brought up the Character Map, and sure enough there is an Omega symbol in Arial font. This should work. Thanks!