• Portfolio
• Shop
• Blog
• Profile
• Contact
• Gallery
• Twitter
• Subscribe

1. 
   
   Nathon Dalton July 6th, 2009

   I have been searching forever online trying to find a format for use with email addresses. It doesn’t need to make it a clickable link. It’s simply to assure people enter a valid email address. Would you mind telling me how to do this and maybe add it to your site for others?
2. 
   
   Simon Page July 7th, 2009

   Nathon here you go:
   http://simoncpage.co.uk/blog/2009/07/07/excel-vb-validate-email-address/
3. 
   
   Andy August 15th, 2009

   Thanks so much – I have been looking for a simple explanation of this with examples for so long!
4. 
   
   Rishav Sethia December 12th, 2009

   Hi,
   I have been trying to frame a custom number format in excel for displaying a number in Hundreds (eg. 12,34,56,789.00). The same is usually displayed in thousands (123,456,789.00).
   Pls help me on this.
   Rishav
5. 
   
   James December 15th, 2009

   Hi,

   I’m trying to format data to a different numer of decimal places demending upon the value. For example within the range 0.001-0.01 to 3dp, from 10.0 to 50.0-1dp and from 100-1000 to no decimal places. Any suggestions you have would be really helpful!

   Thanks,

   James
6. 
   
   Simon Page December 16th, 2009

   @ Rishav

   Couple of options for Indian Rupee currency formatting:

   1. Custom cell formatting with limitation:
   [>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;####0.00

   2. VB code as Worksheet_Change event
   If however this doesn’t work then why not try this vb module to add in the worksheet:

   Private Sub Worksheet_Change(ByVal Target As Range)
   ‘//Written by parry
   Dim c
   If Target.Cells.Count = 1 Then
   Select Case Target.Value
   Case Is >= 1000000000
   Target.Cells.NumberFormat = “##”",”"00″”,”"00″”,”"00″”,”"000.00″
   Case Is >= 10000000
   Target.Cells.NumberFormat = “##”",”"00″”,”"00″”,”"000.00″
   Case Is >= 100000
   Target.Cells.NumberFormat = “##”",”"00″”,”"000.00″
   Case Else
   Target.Cells.NumberFormat = “##,###.00″
   End Select
   Else
   For Each c In Target
   Select Case c.Value
   Case Is >= 1000000000
   c.NumberFormat = “##”",”"00″”,”"00″”,”"00″”,”"000.00″
   Case Is >= 10000000
   c.NumberFormat = “##”",”"00″”,”"00″”,”"000.00″
   Case Is >= 100000
   c.NumberFormat = “##”",”"00″”,”"000.00″
   Case Else
   c.NumberFormat = “##,###.00″
   End Select
   Next c
   End If
   End Sub
7. 
   
   Simon Page December 16th, 2009

   @ James

   Couple of ways you could use a custom formatting (not ideal though as it is very limited):

   [< =0.001]0.000;[<=50]0.0;0

   Or preferred route using a VB module like this:

   Sub FormatDP()
   Dim cell As Range
   For Each cell In Selection.Cells
   If cell.Value <= 0.01 And cell.Value >= 0.001 Then
   cell.NumberFormat = “0.000″
   End If
   If cell.Value < = 50 And cell.Value >= 10 Then
   cell.NumberFormat = “0.0″
   End If
   If cell.Value < = 1000 And cell.Value >= 100 Then
   cell.NumberFormat = “0″
   End If
   Next
   End Sub

   (simply add this vb code in as a module then you just need to select the cells you want to format and then run the vb module).

   HTH – all the best
8. 
   
   Jim August 26th, 2010

   Hi,

   You’ve got a great page here. So far the best I’ve been able to find. Thanks for making this information public. I have an issue that I’ve not been able to find resolution to. I’m trying to develop a custom number format that will display numbers provided in thousands as whole numbers. In other words, the spreadsheet says 1,000.0 and I’d like to display it as 1,000,000.

   I’m trying to do it through custom formating because I have a different application that I’m going to use it in that actually recognizes excel custom formats.

   I’d appreciate any wisdom you might have.
9. 
   
   LarryFromVegas October 20th, 2010

   This one is challenging.
   I want a cell to be formatted as % if value 100. I tried using custom format:
   [>100] $#,###.00;[100 and 10000, it displays the $ sign but the value shown is 100 fold too small. If I type in 1234567, I get $12,345.67.

   If this can’t be done with custom formats, is there a w ay to do with Conditional Formatting? I couldn’t get Conditional Formatting to do any formatting other than color changes.
10. 
    
    LarryFromVegas October 20th, 2010

    I’m reposting. The dialogue box clipped some of long sentences,
    so I’m re-posting with
    to break up longer sentences.
    Sorry for inconvenience.
    ============================

    This one is challenging.

    I want a cell to be formatted as % if value 100.
    I tried using custom format:
    [>100] $#,###.00;[100 and 10000, it displays the $ sign
    but the value shown is 100 fold too small.
    If I type in 1234567, I get $12,345.67.

    If this can’t be done with custom formats,
    is there a way to do with Conditional Formatting?
    I couldn’t get Conditional Formatting to do
    any formatting other than color changes.
11. 
    
    anne March 18th, 2011

    I would like the cell to be formated so that when I enter a number for exemple 8 it shows 1

    I am basically trying to insert the number of hours worked and displaying it in days. Assuming a days work is 8 hours.

    I want to to be in cell formating so that I do not have to indert a formula each time I fill the cell

    thanks
12. 
    
    Scott Gall January 13th, 2012

    Can a customnumber format condition based on error codes?

    for example if the cell’s value is #N/A make the number format “”?
    or if the result is #DIV/0! make it show “Cannot Divide by 0″… etc

    Thanks in advance.

Powered by a Space Invader ©2008 - 2012 Simon Page. All Rights Reserved.
This page took 0.440 seconds to load.

Flickr Twitter RSS Archives Contact