Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Custom Number Format (hundred-thousands, Millions)

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.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Millions & Thousands Custom Number Formatting
To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.

[>=1000000] $#,##0.0,,"M";[

View Replies!   View Related
Custom Cell Format In Which Space Follows Thousands Place
I am trying to write a custom cell format that would allow me to replace the comma with a space after the thousand's place. Therefore, 1 million would look like: 1 000 000 instead of 1,000,000. I do NOT want to change my regional settings to accomplish this.

When I write ### ### ### it accomplishes this, however, there is one small glitch. When you apply an "underline" you get the following result:

_1 000
_10 000
_100 000
1 000 000
10 000 000
100 000 000

The underline does not lineup evenly with the "ones" digit until we reach 1 million. I want to write a universal format in which the underline lines up evenly regardless of the number.

View Replies!   View Related
Formula For Several Hundred People With Several Hundred Choices
I'm trying to write a formula for the following simple situation. Eventually I will need to use this formula for several hundred people with several hundred choices.

Like I said, this is a simplified example that I will eventually need to expand out to hundreds of people. Lets say I have 5 people, we will call them #1, #2, etc to #5 and I have 5 cars, red, blue, black etc. Person #1 gets his first choice, person number 2 gets second choice etc. But if person #2's first choice is the same as person #1's, obviously he can't have that choice and must go to his second choice. Some people may not have a preference and won't get assigned any color.

View Replies!   View Related
Custom Number Format ...
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 #,###

View Replies!   View Related
Custom Number Format..?
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.

View Replies!   View Related
Custom Phone Number Format
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 [

View Replies!   View Related
Number Custom Format A Cell
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)

View Replies!   View Related
Conditional Custom Number Format
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.

View Replies!   View Related
Custom Number Format Multiply
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?

View Replies!   View Related
Custom Number Text Format
Data is: A1B2C3. I need:A1-B2-C3. Custom format 00-00-00 only works on numeric data?

View Replies!   View Related
Custom Number Format With Condition
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.

View Replies!   View Related
Custom Number Format For Displaying MAC Addresses
I'm trying to create an Excel sheet to display MAC addresses in the following format -


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.

View Replies!   View Related
Alter The Number Of Decimals In A Custom Format
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 ?

View Replies!   View Related
Scale A Number Using Format Cells Custom?
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 cant 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.

View Replies!   View Related
Custom Number Format With Wrap & Text
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.

View Replies!   View Related
Save/Store Custom Number Format
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?

View Replies!   View Related
Format Currency In Thousands,lakhs
i want to know how to numberformat numbers (Currency) into thousands,lakhs.
For example i m having amount in colmun D like 1239.00 i want to format the numbers in this column as Currency with thousand seperator and lakh seperator. like1,239.00 or 1,23,900.0

View Replies!   View Related
Format TextBox With Thousands Separators
We have created a userform with several textbox for numbers. We'd like to show them with a format similar to "###,##0.00" and allow the users to enter numbers without commas, but always show the textbox formated. Is there any special way to manage this format? As the textbox is a "text" value, how can we manage the input and the numbers? Should we always replace the "," withi a nullstring? Example:

Private Sub tbCakPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
tbCakPrice.Text = Format(Val(tbCakPrice.Value), "######0.00")
tbPxCakCnFCgl.Value = Format(Val(tbCakPrice.Value) + Val(tbFleteRealCgl.Value), "########0.00")
End Sub.......................

View Replies!   View Related
Custom Number Format :: To Contain (and Show) Both An Interger And Text
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?

View Replies!   View Related
Custom Number Format For Negatives & Include Text
This is my formula and it returns: -55 bps.
=[ASK.xls]QTR!$AT$81&" "&"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.

View Replies!   View Related
Custom Number Format To Show Difference In Two Dates
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.

View Replies!   View Related
Custom Format That Includes Text And Numbers, Such As Medicare Number
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.

View Replies!   View Related
Number/Text Format: Custom Formate "0000-0000-0"
I have this data 0123-4567-8 but in the formula bar it only shows as 12345678. When I looked at the format it has as Custom 0000-0000-0 and it would be correct. The formula that I used to extract the numbers without the dash is =LEFT(A3,4)&MID(A3,6,4)&RIGHT(A3,1) the result is 12345678 So when I reference this number to 012345678 I get the wrong answer.

View Replies!   View Related
Finding Duplicates Out Of Millions
I have to scrub files of 20,000 phones numbers against a file of several million phone numbers on the national do not call list.

On sheet one I have all 20,0000 phone number and then on sheet two in 5 columns I have roughly 2 million phone numbers. I need to know if any of the 20,000 phone numbers are in the 2 million on sheet two.

Right now I am simply using a vlookup formula but it is taking a very long time to update all of the fields.

View Replies!   View Related
How Do I Custom Format ###.####.##
I have a 9 digit number that I want to format as 999.9999.99 Can anyone tall me how? Does anyone know a good link with pointers on custom formats?

View Replies!   View Related
Lookup Function For Millions And Billions
Nano Caps are companies with a market value below $50 Million
Micro Caps are companies with a market value between $50 Million and $300 Million
Small Caps are companies with a market value between $300 Million and $2 Billion
Mid Caps are companies with a market value between $2 Billion and $10 Billion
Large Caps are companies with a market value between $10 Billion and $200 Billion
Mega Caps are companies with a market value above $200 Billion.

I have this part so far that someone helped me out with that belonged with this criteria:

Micro Caps are companies with a market value below $300 Million
Small Caps are companies with a market value between $300 Million and $2 Billion
Mid Caps are companies with a market value between $2 Billion and $10 Billion
Large Caps are companies with a market value above $10 Billion


I came up with this one for the new criteria:
=LOOKUP(P2,{0,50000000,300000000,2000000001,10000000001,200000000001},{Nano,"Micro","Small","Mid", "Large",Mega})

it keeps giving me an error message..... the thing is the criteria that its based off of( market cap) is in millions but this is the function thats in the cell: =K2*T2/1000000

View Replies!   View Related
One Hundred Back To 100
I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" .

But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number

View Replies!   View Related
Date Custom Format
How do you make custom date format to a cell in such a way that it appears e.g. "Jan-06"?

I tried custom format "Mmm-yy" but it still resulted to "jan-06".

I want the "J" here to be in the upper case.

View Replies!   View Related
Custom Format For TEXT
I have a column in my sheet which I need to be displayed with dashes.

Excample: 123456 -> 12-235-6 (just an example)
But once in a while in the code is a letter like: 123A56 which obviously I need to be displayed like 12-3A5-6

But whatever I do, nothing seems to work.
Another cell with Left()-Mid()-Right() is no option for me....

View Replies!   View Related
Leading Zero(s) / Custom Format? ()
I'm having trouble with the leading zeros of my ISBN#s; excel keeps chopping them off. I found a topic that discussed this issue, but it won't work for me.

I can't format them as text because I'm getting them as an isbn with dashes (ie:978-05689-256-7) and when I remove the dashes (even if its formatted as text) excel changes the number to 9780568.9E+12 (or something like that)

I think the solution would be to create a custom format (ie. 0000000000) to preserve the zeros, but I'm having 2 problems:

1. Since the ISBN#'s have 2 different formats (ISBN-10 & ISBN-13) I need to somehow check if the first 3 numbers are 978 then based on that it should be formatted either as ISBN-10 (0000000000) or ISBN-13 (0000000000000)

2. In the post that I linked to above it said that I won't be able to do a vlookup if its custom formatted. Is there any way around this?

View Replies!   View Related
Custom Cell Format
I'm working on a workshet where ppl fill in vehicle numbers, but they all find their own unique ways to fill them up. Some of the examples are below:

MH 04TN 2315
HR 38G 9605
GJ04 GA5142
KA 0072

3,4,7 are incomplete nos. while 1,2,5,6 are all complete nos. I want the format of these above nos. to look like this:


Can this be done automatically in a worksheet using the custom cell format function??.... because then it wud get formatted as necessary whenever someone enters a vehicle no. in that particular column.

View Replies!   View Related
Custom Date Format
I have not learned the custom number format feature. If someone could give me the format for the cell to display:

4/23 Wed

View Replies!   View Related
Custom Format Wildcard
Anyone know if a wildcard can be used in Custom Formatting using text leading off. Example:


I have the above custom formatted as: "U"00-00-0000 which gives me U01-23-4567. This give me the format I need however; the U is not always constant and needs to be changed to different letters as required.

View Replies!   View Related
Custom Format Interferes With Itself
I have a custom format that is applied to column AU. Here is the worksheet code which is placed in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 47 Then Exit Sub
If Target.Value = "" Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & ">"
Application.EnableEvents = True
Exit Sub

Application.EnableEvents = True

End Sub

It works well on the sheet, if I remember to only put three numbers, three letters and then two more numbers (allwithout any spaces. Lowewr case ok). (eg. 122rmm25 is then transformed into Map 122R <MM-25>). On the sheet this works. Coming off of the UserForm, it tries to apply this format to text already formated and messes up royally!!!

Is there any way to either reverse this proccess coming off the UserForm or to stop the worksheet from appling this format to the value of the cell in question if the source is in the wrong format to be changed? How do I detect what format the text is in? (Raw, formatted correctly or over formatted)

View Replies!   View Related
Custom Date Format: For 2 Dates
I want to display 2 dates in one cell and have them custom formatted.

Eg. Display in one cell: 26 Jun - 27 Jun

With text typed: 26-6 - 27-6
(Or something along the lines of)

I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.

View Replies!   View Related
Japanese Currency Custom Format
I would like to set the format of a cell to the Japanese currency format. This means that the comma separator occurs at 4-digit interval instead of 3 (ten-thousand separator instead of thousand separator) like this:

1,000 -> 1000
10,000 -> 1,0000
1,000,000 ->100,0000
1,000,000,000 -> 10,0000,0000

and so on...

There are some rules with the decimals, but I will leave that out for now.

how to achieve this with either custom format or VBA, instead of initiating system changes?

View Replies!   View Related
Custom Format (insignificant Values)
I am currently using this format to display numbers within a pivot table (this put's in a comma and removes any decimal place):

_(* #,##0_);[Red]_(* (#,##0);_(* "-"??_);_(@_)
Scenario1-If the cell evaluated is 0>1 the cell displays: 0 (a black 0)
Scenario2-If the cell evaluated is 01 are insignificant and I would like them to remain as a black - so as not to draw attention to them while evaluating data within the PT.

Does anyone know of a way to display a black - in all three scenarios above short of editing the original data?

I was hoping there was a way to do this via the custom format option or within the pivot table as the data that produces the PT should remain untouched.

View Replies!   View Related
Custom Format For Phone Numbers
I'm trying to format some cells, its some phone numbers but not in the traditional format (###) ###-####.

I need to get them to appear like this ###.###.####,

View Replies!   View Related
Custom Date Format To Be Retained In .csv Also
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv

Here is the code i am trying to modify without much success so far

Sub dateFormat()

Dim dtMydate As Date
Dim sMydate As String
Dim strWhen As String

strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string
'strWhen = Cells. Find("As on")

'take out the date portion from strWhen
strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))

dtMydate = CDate(strWhen)
Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM

dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss")
Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...

View Replies!   View Related
Custom Format In Cell From Userform Textbox
On the attached example, there is a user form with a text box and an enter button.

What i want, is when i press the enter button, the value in the text box goes into cell B2 on sheet1 in the format of "?/???".

View Replies!   View Related
Custom Date Format With Month All Caps
I am trying to get a custom date format that looks like:

I can get 30May07 with ddmmmyy but can't seem to find how to get the 3 letter month to display in caps.

Excel 2002 SP3 on WinXP

View Replies!   View Related
Auto Fill Custom Format Numbers
I have the following list of numbers

0000 0000 0000 0000 0000 0100
0000 0000 0000 0000 0000 0101
0000 0000 0000 0000 0000 0102

and I need to continue the list to

0000 0000 0000 0000 0000 0326

I tried dragging by the grip in the bottom right hand corner but it changes the first didit only. I tried to put it the numbers in the format


and using a custom format number to put in the extra zeros. This worked fine until I used a countif (or it may of been a lookup) statement in my code to look for 0000 0000 0000 0000 0000 0100 and it can't find it as the number is 100. Is there away around this that will save me typing in an awful lot of zeros.

View Replies!   View Related
Custom Format To Replace Decimal With Comma
I am having problems applying custom format to numbers that need to be formatted.
custom format use is 0",00". When I apply the custom format above to whole numbers, I achieve the results I want:




However, when I apply to numbers with decimal places


how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?

View Replies!   View Related
Create Custom Serialized Date Format
I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001). I've tried using both a formula and custom formats but cannot get the cell contents to display as desired. I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.

View Replies!   View Related
Custom Format, Combined Date, Time And Letter
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?

View Replies!   View Related
Recalculate Custom Function With Cell Format Change
I saw there were codes to sum or count cells that have a specified Fill Color

However it says the changing of a cells fill color will not cause the Custom Function to recalculate, I really need to recalculate the data if there are any changes in the selected range

View Replies!   View Related
Spreadsheet For A Couple Hundred Rows Of Data And 6 Columns
I have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows

Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.

View Replies!   View Related
Copy The Block Of Formulas And Formats Down The Sheet A Few Hundred Times
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.

View Replies!   View Related
Save Number As Text In Txt Format Wo Changing The Format
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.

First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.

View Replies!   View Related
Custom Number Formatting In 2,1.5,0.5,10,19.5
I would to show the number like this if it hasn't decimal like 2 showing in 2 not 2.0 and if it has decimal like 1.5 show 1.5, 1.75 show 1.75....
Now I try this "_(* #,##0.0_);_(* (#,##0);_(* "-"??_);_(@_)"

View Replies!   View Related
Copyright 2005-08, All rights reserved