Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Custom Number Format For Pounds

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

View Answers     

Similar Excel Tutorials

Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...
Excel Time - Summing Time Greater than 24 Hours
I'll show you the easy and simple way to sum time past 24 hours in Excel. All we really have to do is to change th ...
Format Cells in The Number (Numerical) Number Format in Excel
This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that th ...
Format Cells in The Short Date Number Format in Excel
This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format di ...

Helpful Excel Macros

Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as a Currency in Excel Number Formatting
- This free Excel macro allows you to quickly and easily format a selection of cells in the Currency number format in Exce
Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format

Similar Topics

Hi Guys,

I was wondering if it was possible to make a custom Format in the Format Cells box.

I wanted to do a weight loss spreadsheet using the Imperial measurements. The UK guys will know what I mean.

Here in the UK we usually use Stones and Pounds for example 12 Stone 5pounds(lbs).

Now there are 14 pounds to 1 stone. So how I have had to do this is have the stone and pounds in two separate cells. and then have a 3rd cell that will take the value in the 'stone' cell and times that by 14 and then add the pounds value to that. In the above example of 12st 5lbs, the total value in pounds would be 173lbs.

To reverse this I simply divide 173/14 rounded to 0 decimal places to ensure a whole number. Then to get the pounds value I times that 12 stone by 14 to get 168, once I have that figure I take the 168 away from 173 and I get my 5 pounds.

What I wanted to know is, could a VBA code be made that would do all these calculations in one cell and then I could have a custom format say in the above example 12-5 and then the VBA code would treat both figures either side of the '-' as separate numbers?

Can this be done?


Hey everyone,

Basically what i want to do is enter a number into a cell and have it display the number, but then also label it as "lbs" for pounds.


i type 1000

it formats to 1,000 lbs

i figure this could be done if there was a way to add a custom format style, but i'm not sure how to do this. any help?



I am trying to convert a fractional measurement of lbs into pounds and a fractional ounces. For example I want to convert 69 1/8 to a cell that says "4 lbs 5 1/8 oz" The conversion itself isn't a problem, I have a cell that finds the pounds and a cell that finds the oz's, but I can't figure out how to join them. Using either CONCATENATE or & I get "4 lb 5.125 oz" Can someone please tell me how to do this. I've bee trying to play with custom formats but that hasn't worked either, but I honestly never use that so it may be lack of knowledge on my part trying to use custom formats.


I have a suite of reports on 50 excel sheets, calculating data from one main source. The results are in pounds, but I want to convert it to £'000s without the need to use a formula. I am sure there is a custom data format that I could use. Can anybody help?

I have a formula that I would like to display the result as pounds and
ounces. I created and custom format that will display as (for example) "14.25
lbs" but I would like it to display as 14 lbs 4 oz. It must display as a
number and not text. Any help would be great!


I have a table of conversions.

for example:

3.60 pounds in cell a1. cell a2 is to take the .60 pounds remaining in cell a1 and convert that to ounces, 9.6.

When this happens I want the pounds to only display the whole number rounded down.

so: 3 pounds in cell a1 and 9.6 ounces in cell a2.

The problem is when I used the rounddown function in a1 to make it display the whole number, cell a2 reads it as 0 becuase theres nothing left to convert. When I used the format cells function to display 0 decimals it rounds it up sometimes. I always need it rounded down. What should I do?


I have this function to format the input depending on a min-max limit...

between 0 and 5 pounds, write 0;
between 5 and 10 pounds, write 5;
between 10 and 15 pounds, write 10;
between 15 and 1000 pounds, write 15;

Here is the function:


I am creating a simple spreadsheet to catalog some magazines. One of the columns shows the cover price which is okay for post 1970 magazines as it is in Pounds Sterling, but is there anyway I can get Excel to recognize Pounds/Shillings/Pence as currency. So put in "8d" for example without setting the field to a text format?

I want to be able to add up the total cost of some items based on their cover price but if I set the required cells to text format I cannot do this automatically using the Sum forumla.

Can anyone tell me if it is possible to have both Pounds Sterling and Pounds/Sillings/Pence as currencies in the same table or is using a text field for the latter the only option?


Hi There

I have a fancy number format which I like to use looks like this:

_(* #,##0.0,_);[Red]_(* (#,##0.0,);_(* "-"_);_(@_)

I would like to add it as a custom number format, the problem is if
I go format-cells-number-custom and add it to the type, this works great while I am in the workbook but if I close the workbook, when I reopen another workbook or open another session on excel the custom number format is no longer there.

Does anyone know how I can add a custom number format so I can keep using it regardless if I close excel and reopen.

Kind Regards


Hi all,
I'm pretty confused about the custom section of Format Cell Number.
Below is the custom code I get from, when I select category accounting Rp.
_([$Rp-421]* #,##0_);_([$Rp-421]* (#,##0);_([$Rp-421]* "-"_);_(@_)

Can somebody tell me why in order to format a number into Currency with aligned space for Currency symbol and the amount need to use that code in custom?
Anyone can give me knowledge about custom number format so I can understand and in future will know what I want and how to code.

Now, the solution I need urgently is how to custom format that code, so when a 0 (Zero) is entered into a cell, the cell display blank and not Rp. -

Thank you

Hi all,

Here is an example of what I'm trying to do, and how I would like the data displayed:

A1=178 and I want to get to: B1=12st 10lbs

Now, if I use the formula =A1/14, I get the value 12.71. Is there a way of converting this into the above format?

For display purposes, I can probably use a custom number format, but I first need to get the right value into B1.

How about separating the 12 from the .71? Then I could display the two values separately in the same cell, after using a formula on the .71 to turn it into 10. Is this possible?

Thanks a lot for any advice!



I am looking or a formula that will take decimal pounds and display pounds-ounces, not even sure that excel can display pounds-ounces. I am fairly good with formulas but beyond add, subtract, multiply and divide I am clueless.

A very simple example of what I am looking to do:

cell A1 is entered as 1.5
I want a formula in cell A2 that will display 1lb-6ozs


Hi, I feel so stupid because this is such an easy formula. Its just my
brain isn't working lately (been sick) and its driving me crazy.
Anyway, this is the problem
A1 = number of cards
A2 = how many pounds the cards weigh
1,000 cards = 8.5 pounds
So if I have 1400 cards that would be like 11 pounds.
So I need to make an ongoing chart of this.
Please help! thanks so much. (I hope that made sense)

I have 2 columns for weight.
Col A is in pounds
Col B is in ounces
16 ounces = 1 pound

The columns are totaled using @SUM. Typical totals are 12 pounds and
37 ounces. I want the next line to show the correct presentation which
is 14 pounds and 5 ounces.

How can this be done?

I have the following #: 1,234,567
I want the format to be 1.23
How do I do that?

I've gotten to the thousands with the following:
, but I can't get it to the millions.
With the above custom format, I get 1.23 if my value is 1,234.

I am NOT going to just divide my number by 1,000,000 to get my desired result so please do no suggest that. I need a custom format solution. Thanks.


I've got a column that displays a series of weights in stones & pounds (both
in the one cell). How do I get a chart to recognise and display in the same


I'm trying to write a spreadsheet to calculate the pounds and ounces of different foods to feed my dog, cat, and kitten, based on their weight.

In this thread Marcol helped another poster with a spreadsheet for figuring weights of fish, but when I opened it, the formulas would not display.

Attached is my spreadsheet so far. It's got values in kilograms but my scale only weighs pounds and ounces.

Thanks for helping!

I am trying to custom format cells to display "10-10-001CM" after only "1010001cm" is typed. I have been able to format it with just the numbers but everytime I enter the text it no longer formats. The number format will always be the same "##-##-###AA" The letters may be CM, CW, or CC. Does anyone have a suggestion on how this custom format code? Any help would be appreciated! Thank you!

I'm trying to select the appropriate format for a cell that contains a number. The format available in excel have a selection of custom formats whose description are a series of #!$-%. But I can't figure out how to make sense of these custom formats. Am I able to create one?

In short, I'm trying to format my numbers so that they have 2 decimal places always, if the value is equal to zero to display a simple dash "-" and if they are negative I would like them to show the negative sign before the number (i.e. -0.23).


I am trying to find out how i can set up if statements based on parameter values. For example;

My function will be "Shippingfees" based on the parameter weight

Shipping fee is calculated based on weight.
If it is 1 pound, shipping fee is $2
If it is 2 pounds, shipping fee is $4
After two pounds, fee is increasing by $1 for every pound. But if the weight is 2.3 pounds this will still be based on 3 pounds. So it actually means if weight>2 and weight<=3 fee will add up one more dollar.
If it is , let 's say 5.3 pounds, then the fee will be 8 dollars ($4 + $1 + $1 + $1 + $1) [because it will be recognized as 6 pounds]

My question is how can i set up this in vba?
I'd very much appreciate if anyone could help.

I created a weight loss worksheet. The columns are as follows:

Current Weight
Weekly Pounds Lost
Total Pounds Lost
Pounds to Goal

I have no problems with the formulas for the Total Pounds Lost and the
Pounds to Goal...however, the column with the Weekly Pounds Lost doesn't
change automatically each week I enter in the current weight.

I did copy the formula down...but, I don't want the values to show up until
I enter the weekly current weight numbers/column.

Here are my formulas (the current weight column I enter in the number, the
rest of the columns should update automaticaly):

Column 2 (Weekly Pounds Lost)

=SUM(D9-D10) etc...etc.

Column 3 (Total Pounds Lost)

Column 4 (Pounds to Goal)

So, what formula can I enter into the 2nd column so the values don't show up
until I enter the current weightin column 1? Right now it shows after the
first four weekly current weight values entered as:


I hope I explained this well!

....also, if you can be patient with me...I'm not an Excel expert...just know
BASIC formulas!

Thank you for ANY help!!

I am working on a sheet that logs sales enquiries and quotations. The quotations may be made in either Dollars, Euros or Pounds. However, I want the 'reporting' column to be Pounds. I would appreciate any advice on the simplest way of achieving this.

Imagine column A is dollars, column B is Euros and column C is pounds. Most of the time the quotations are done in pounds and the other two columns will be blank. Also, there will only ever be one figure quoted per row. Would it be easier to create a fourth column (D) to consolidate the three separate figures together? We are using fixed exchange rates for the year, so to convert to pounds we will multiply any dollar quotations by 0.5 and any euro quotations will be multiplied by 0.69.

Many thanks,

I am entering daily totals for my milking cows' production. I would like to have a current daily average showing after I enter each day's total. My problem is that the number of days the total production is divided by is variable, based on how many days each cow has produced milk in each month.

I have a generic template that I use for each month.

Column A has 1-31 listed down, starting at A4,
Column B has Cow #1's total pounds
Column C has Cow #1's total gallons, which is column B divided by 8.6
Column D has Cow #2's total pounds
Column E has Cow #2's total gallons, which is column D divided by 8.6
Column F has Cow #3's total pounds
Column G has Cow #3's total gallons, which is column F divided by 8.6

B1 has Cow #1's total pounds for the month
B2 has Cow #1's total gallons for the month

D1 has Cow #2's total pounds for the month
D2 has Cow #2's total gallons for the month

F1 has Cow #3's total pounds for the month
F2 has Cow #3's total gallons for the month

Row 3 has headings

I was thinking of somehow defining how many cells in each column has data in it and subtracting that from the first 3 rows of heading and totals and dividing the total pounds by that number it would give me a daily average, but I have no idea how to do that. Any help would be appreciated.

Thank you so much for any help you can give. If anyone want's to see the worksheet, I would be happy to e-mail it.

Shalali, the FarmGirl

Hello. I am using a custom number format ($0.0,,). I want to be able to hide all Zero Values, or at least use a '-' character instead of getting a bunch of 0.0 numbers in my table. Is this possible while still retaining my custom number format ($0.0,,).


Is it possible to have a custom number format where all these factors are possible: 0 equals -(dash mark), 0.01 through 0.09 equals T (for Trace), and everything greater than 0.09 is formatted as 0.0. This is my original custom format _(*#,##0.0_);_(*#,##0.0);_(*"-"??_);_(@_). It allows for the 0="-" and 0.0. And I was able to get the T part by using this format: [=0.9]"T"*0, but I am not able to get all 3 parts working together. Any help is appreciated. Thanks.