# Scale A Number Using Format Cells Custom?

Aug 6, 2009

I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)

Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.

I saw in another forum thread to use #”.”###. This does the scaling but then I can’t make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.

## Custom Number Format For 0 (zero) Number - Make It Center Alignment

May 11, 2014

i am looking for excel custom number format for 0 (zero) number that make center alignment..

for example ;

sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)

how make center alignment with custom number format for 0 (zero) number..

## Custom Number Format..?

Apr 22, 2009

If I have the following info:

A1 = 5
A2 = -4
A3 = 0

How can I use Format Cells > Custom to have them display as:

A1 = 5 up
A2 = 4 dn
A3 = Level

I've been trying and trying but keep getting a message that I should use one of Excel's pre-defined formats.

## Custom Number Format?

Mar 25, 2014

Looking for a custom number format which will show numbers as follows:

0 = -

1234.89 = 1,235

-1234.43 = (1,234)

## Custom Number Format ...

Jan 16, 2008

This workbook has 23 sheets for which this format will apply.

Under 1 man-hour .###
Over 1 man-hour #.##
Over 10 man-hour ##.#
Over 100 man-hour ###
Over 1000 man-hour #,###

## Conditional Custom Number Format

Mar 6, 2009

I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.

for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ;
or: [>999]###.0,kÙ;[<1000]###Ù

both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.

## Number Custom Format A Cell

May 31, 2009

is it possible to custom format a cell in order to:

a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less)
b) Present 3.333 as 3.33
c) Present 3.33 as 3.33
d) Present 3.3 as 3.3
e) Present 3 as 3 (NO zeros and NO decimal dot)

## Format Custom - Padding Zero (0) Number

Feb 19, 2014

Format custom number setting for this below ; with condition, when I type following ;

1------001/2014 (showing data)
8------008/2014 (showing data)
45-----045/2014 (showing data)
17-----017/2014 (showing data)
364----364/2014 (showing data)

etc....

## Custom Number Format - Em Dash For Zero

Dec 19, 2011

I know this works because I have seen it in a workbook at a client's office.

I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.

I want to be able to set this up as a custom number format in the custom number dialog.

## Hide Number One With Custom Format?

Feb 28, 2012

Instead of using conditional format, I would like to use a custom format to hide the number 1. Is this possible?

Basically only three things can be in this cell =IF(D8"",IF(D8="CIV",1.077,1),"")

I need to mask the 1, otherwise show the 1.077. The reason I am not using conditional formatting is because there is a condition on the cell already which applies a pattern style and a font will not match up to the pattern.

## Custom Number Format - Percentage

Jul 8, 2014

I am trying to create a custom number format.

Here is what I have come up with: 0.00%;[Red]-0.00%

The problem is, it has no "," it would display as 23555555.6% or -2355555.6%

I need it to display as: 23,555,555.6% or -23,555,555.6%

How would this be written?

Additionally, I have occasion to "abbreviate" in the following format:

23.55MM% or 1.5M% or -23.55MM% or -1.5M%

For currency, I have it written in this manner:

Currency: [>=1000000] \$#,##0.0,," MM";[>0] \$#,##0.0," M";[Red](\$#,###," M")

I just freeze when trying to convert to percentage.

## Custom Phone Number Format

Mar 3, 2009

I am looking for a custom number format for phone numbers that will allow extensions to be typed in. I am trued using the standard phone format [

## Custom Number Format With Condition

Nov 2, 2006

I am trying to work out a custom number format to display betting fractions properly - namely 3/2 as 6/4 and 2/3 as 4/6. The following works for 3/2:

[=1.5]"6/4 " ;??/??

However I can't get anything to work for 2/3 as it is a recurring decimal.

## Custom Number Format Multiply

Apr 26, 2007

Is there any way to get a custom number format to display a multiple of the cell value? e.g. can I have a cell have the value 0.005 and display the value 50?

## Custom Number Text Format

Oct 5, 2007

Data is: A1B2C3. I need:A1-B2-C3. Custom format 00-00-00 only works on numeric data?

## Macro Remove Custom Number Format

Apr 24, 2014

i am looking macro code to fixing value/number from custom number format (with dot mark criteria) as my data and will be as displayed fixing (what you see if what you get)....

2004.09.000907 (custom format) --------- 2004.09.000907 (after using macro)
1998.08.000001 (custom format)----------1998.08.000001 (after using macro)

if you press F2 in the sample number will be not visible dot (.) mark..

how to make/do that the number look like using custom number but fixed (remove custom number format)..

it's possible using macro?

## Can Custom Number Format Be Applied In A Macro

Jun 4, 2013

We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?

## Custom Number Format For Displaying MAC Addresses

Oct 18, 2005

I'm trying to create an Excel sheet to display MAC addresses in the following format -

00:00:00:00:00:00

However. the characters can be either letters or numbers. I've modified the "hh:mm:ss" template to accomodate the format I need, but it will not recognize letters for the obvious reason. Is there a wildcard character that I could use in this format or has anyone created a format that would meet my needs? If I can pull this off, it could mean a promotion.

## Alter The Number Of Decimals In A Custom Format

Oct 24, 2008

is there any way by which you can alter the number of decimals in a custom format using just custom format process (ie no VBA).

For ex:

12 should appear as 12
12.1 should appear as 12.1
12.26 should appear as 12.3

Note 12 should NOT appear as 12.0

So point being adding the "m" is an irrelevance at this time - the key is to vary the decimal points essentially based on MOD(value,1) = 0

If you can (I'm really thinking you can't) let's extend it such that 12.1 should appear as 12.1 but 12.26 as 12.26

I know you can use [value>x] type rules but I'm guessing you can't use formulae in those rules ?

## Custom Number Format With Wrap & Text

Sep 14, 2007

I have been playing with custom formats recently and I'm wondering if there is anyway to make them wrap?

specifically I have several results that I have been formatting to show what they are related to

397235 visitors 392648 visitors 365487 visitors

I want these to display as visitors, but still be numbers so that they can be summed etc.
The problem is that I need the text to wrap so that visitors is on the second line in the cell. unfortunatley all i get is a ########## error to show the cell is not wide enough even with wrap text turned on.

## Save/Store Custom Number Format

May 7, 2008

From what I find in Excel and from what the Microsoft website instructs, there isn't an obvious way to save a custom number format without having to create a template to store it.

Has a way been discovered to convince Excel to save a new number format and include it in the drop down selection for any workbook?

## Custom Number Format (hundred-thousands, Millions)

May 22, 2009

I have a giant sheet that culminates in a graph. It is for various regions. When used in a country with in-control currency, you end up with a nice healthy number that looks good on the graph. When our Zimbabwe office uses it with their crazy inflation and high prices, we get a crazy number that goes into scientific notation...

Anyway, my question:

Given a number like 4,200,000 or 6,500 or 345,123, I want to be able to show as few trailing numbers as possible, and give a nice K or B or D or however you abbreviate Quadrillion (kM? for Zimbabwe) after it.

And, if possible, I'd like to do it with number formats, but I am flexible so long as it doesn't use VBA.

The lookup table would be:

Sheet1  AB1<1,000As is21,000K31,000,000Mil.41,000,000,000Bil.5>1,000,000,000,000Tril. Excel tables to the web >> Excel Jeanie HTML 4

All numbers have 2 significant digits, and I want to keep 2, so if it ends up as 4,200,000, I want the answer to read 4.2 Mil, etc.

I tried doing this with a lookup, and couldn't handle it.

## Custom Number Format :: To Contain (and Show) Both An Interger And Text

Nov 29, 2009

I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell.

Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell.

a procedure for format ing the cells in the row?

## Custom Number Format To Show Difference In Two Dates

May 11, 2006

I'm trying to get an excel custom number format to show the difference in two dates as 0 years and 0 months...for e.g.

Cell a1 = commencement date = 1/1/05

cell a2 = expiration date = 12/31/10

Then cell a3 = Lease Term = 6 years 0 months.

## Custom Number Format For Negatives & Include Text

Aug 22, 2006

This is my formula and it returns: -55 bps.

However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.

## Custom Cell Format - Number Ending With Text String

Jun 13, 2013

How to modify a custom cell format to make data entry easier.

My goal is to have the output look like this: 'A12B-C20D: SET 10: Text string here'

Inputs needed are:

1-3 digit number between A & B
1-3 digit number between C & D
1-2 digit number after SET
Text string of variable length

Where I am stuck. So far I can get "A12B-C20D: SET 10:" to show up properly when 122010 is input. The custom format I am using looks like this: "A"##"B-C"##"D: SET "##": "

When I try to incorporate the "@" symbol the first part of the formatting goes away and the output shows up as "122010 Text string here".

I have tried:
"A"###"B-C"###"D: SET "##":";;-;_@
"A"###"B-C"###"D: SET "##": ";;@
"A"###"B-C"###"D: SET "##":";_@
"A"###"B-C"###"D: SET "##": ";;;@

## Custom Number Format - Automatically Enter Dashes At Appropriate Spot

May 7, 2014

I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."

To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.

If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.

Is this possible in Excel (without VBA).

I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.

## Custom Format That Includes Text And Numbers, Such As Medicare Number

Jan 19, 2006

how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.

For example, a medicare number.

I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.

## Represent Number Of Days Into A Scale For Statistics

Sep 30, 2008

I have a scenario where by Column A is date incident opened and Colum B is date incident closed.

And Column C shows the days between them. I got this and its fine. However, how do i
make a table or in any how define it as if the number is 1 - 3 days then it should be A

1-3 days = A
4-6 days = B
7-8 days = C
9 days onwards = D

This is because i need to make a chart and graph of the turn around time and the best is to define into a character alphabet so the chart will show the statistics of how many A, B, C and D But how do i do that.

## Format Small Number Of Cells From Text To Number?

Mar 19, 2014

Text to Number or General.xlsx

The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.