Custom number and text format
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Custom number and text format

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a column that contains numbers with 2 decimal places such as 408.00, once in a while I want to enter V08.00. If I format the column in General or Text I lose the decimal places. I also have it set to Data validatation of a number between 1 and 999 with 2 decimal places. Can you please tell me how to format the column so it will display numbers and characters?

  2. #2
    New Member
    Join Date
    Jan 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Because you have numbers and text in the same column you need to use a format that recognises both.

    1) Highlight the column and select FORMAT/CELLS
    2) Click on the NUMBER tab and select the CUSTOM category
    3) In the text box under the TYPE heading enter;
    #,##0.00;[Red]#,##0.00;0.00;@

    To interpret the above, the ; symbol separates each format. The first format is for positive numbers, the second for negative numbers (with a colour), the third format is for a 0 (zero). The final format is for text. The @ symbol represents the text in the cell. You can enter a text format such as (including " character), "The final value is " @

    Hope that this helps out.
    Cheers
    NM

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi margamo and NorthMelbourne:
    I think CUSTOM formatting is not the answer here. He could have the cells formatted as NUMBER with 2 decimal places and that would permit numbers with 2 decimal places, viz 408.00, as well as alpha-numerics such as V08.00 -- I believe margamo's real problem is that he says he has set up a validation rule that says entries must be numbers between 1 and 999.
    So, margamo: if you want to have entries such as 408.00 and V08.00 to be permitted you have to modify your validation rule.

    HTH
    Please post back if it works for you ... otherwise explain a little further and then let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com