Formatting a cell in Excel - how to show a symbol in a specified position

25
2014-04
  • Angelo Rigo

    When formatting a cell in Excel (or LibreOffice), how can I have a certain symbol displayed in a specified position?

    For instance, I want a cell formatted so that the sixth symbol is a hyphen, eg. "2121D-34".

  • Answers
  • John Straffin

    It looks like you can't do what you're you're asking. If your data was purely numerical, a Custom Cell Format of "#####-##" would work fine (displaying "1234567" as "12345-67"). However, as soon as you involve a non-numeric character (e.g. the "D" in your example), this no longer works. One source I found states that, when it comes to formatting cells in Excel, "there is not an awful lot more you can do with text".

    I haven't used LibreOffice, but their help wiki shows that your results there will likely be similar, with plenty of numerical formats but few (if any) text formats available.

  • Excellll

    From the Excel 'Format Cells' dialog:

    "Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered."

    A work-around is to use a helper column that some text formulas. Using your example,

    =LEFT(E1,5)&"-"&RIGHT(E1,2)
    

    will return your data formatted the way you want. If your base data varies, then you'll have to get a little more sophisticated in your formula.


  • Related Question

    formatting - Format a number with optional decimal places in Excel
  • Ray

    I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any.

    For example.

    • 15 should be formatted as 15, NOT 15.00
    • 14.3453453 should be formatted as 14.35
    • 12.1 should be formatted as 12.1
    • 0 should be formatted as 0

    The closest custom format code I've come up with is 0.##. Unfortunately this formats 15.00 as 15. (note the extra decimal point).

    To further complicate the issue, the spreadsheet is a result of an export from SQL Server Reporting Services. So no macros are possible. Oh well, it looks like 0.## is my best bet, and they can just live with the extra period.


  • Related Answers
  • slhck

    Use the following custom format:

    [=0]0;.##
    

    This is basically a conditional format. Pseudocode:

    If CellValue == 0
      Display 0
    Else
      Display CellValue to up to 2 decimal places
    

    I ran this through a few examples in Excel and got these. Does this meet your needs?

         -------------------------
         |Original     |Formatted|
         |-------------+---------|
         |       0     |        0|
         | 12.1234     |    12.12|
         | 12.1278     |    12.13|
         |    12.1     |     12.1|
         | -15.123     |   -15.12|
         |   -24.9     |    -24.9|
         -------------------------
    

    It looks like this doesn't solve the trailing decimal problem either. Sorry for getting your hopes up. My quick searches showed a couple people saying that this one isn't solvable through normal format codes. It needs to be VBA.

  • slhck

    Apply Conditional Formatting for non-decimal numbers.

    For example, A1 is the target cell.

    1. Format A1 as "###,###.00". This will be used for decimal number.
    2. Define Conditional Formatting for non-decimal numbers.

      • Condition: Cell Value equal to =TRUNC(A1).
      • Format: ###,###

    Below is the result:

    12       =>  12
    14.231   => 14.23
    15.00000 => 15
    17.3     => 17.30
    
  • Robert Mearns

    Excel custom formats can provide a partial answer

    Custom formats for numbers in Excel are entered in this format:

    • positive number format;negative number format;zero format;text format

    One format that comes close to your requirement, but leaves in the decimal place for numbers with no decimals is:

    • #,##.??;(#,##.??);0

    Example:

    • 15 is displayed as 15.
    • 14.3453453 is displayed as 14.35
    • 12.1 is displayed as 12.1
    • 0 is displayed as 0
  • Droj

    I ran into this recently in Excel 2007 and just ended up using the 'TRUNC' function in the value cells:

      value  =TRUNC(B5,1)
          0      0
          5      5
        5.4    5.4
      65.43   65.4
    765.432  765.4
    

    Worked exactly the way I wanted it to...

  • Sam

    Here's one method using conditional formatting.

    1. Right click your cell, choose "Format Cell"
    2. Select "Custom"
    3. Enter "0.####" (add more or fewer #s to control the maximum number of decimal places)
    4. Click OK
    5. With the cell still selected, use "Conditional Formatting" (may be a menu item in Format or button in Home depending on your Excel version)
    6. Add a new rule, based on the formula "=MOD(H32,1)=0"

      edit - better formula is "=MOD(ROUND(H32,2),1)=0" with the '2' being the desired number of decimal places. previous formula leave trailing decimal point if the number rounds to an integer.

      Replace H32 in the formula with the ID of your cell, but MAKE SURE THERE ARE NO $ SIGNS! (No $ signs ensures that you to copy the format to other cells)

    7. For the format of this rule, select the number tab, choose "Custom"

    8. This time, enter the formula "0"
    9. Click OK enough times to return to the sheet (varies among excel versions)

    There you go, enjoy responsibly! If you want to copy the format to other cells, remember that you can copy the cell and use "Paste Special" with the "Format" option.

  • Joshua Seigler

    Format cell as 'General' then under data validation restrict values to decimals

  • jonsca

    Alternatively, you can solve the "optional" decimal point problem using the following solution:

    Number Format: General;[Red](General)

    This will add the decimal place and fractional value accordingly, while formatting negative numbers in a more legible way.

    As for the poster's original question, you still need to round/truncate the "extra" decimal points using a formula. However, this is a simple formula of =ROUND(<value>,<desired decimal places>) that is not extremely computationally expensive.

    Examples:

    2500 -> 2500
    0.25 -> 0.25
    -2500 -> (2500)
    -0.25 -> (0.25)
    
  • Siva Charan

    Are you / your users inputting values directly in the cells, or are they being populated by a formula or a macro?

    If the cells are not being populated directly by a human, you could store the calculated values in a hidden range and then display formatted text to the user with a formula like this:

    =IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))
    

    (where 'A1' is the cell being referenced)

    The formula will display values like this:

     -------------------------
     |Original     |Formatted|
     |-------------+---------|
     |         15  |       15|
     | 14.3453453  |    14.35|
     |       12.1  |     12.1|
     |          0  |        0|
     |    -15.123  |   -15.12|
     |      1.004  |        1|
     -------------------------
    

    NB: The formula output is a text string, not a numeric, so:

    • The output defaults to being left-aligned.
    • You cannot use the output in any further calculations (instead, you should use the original cell being referenced)
  • Simon
    B1=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.##"))
    C1=IF(ROUND(B1,2)=INT(B1),TEXT(B1,"0"),TEXT(B1,"0.##"))
    

    No more orphan decimal points.

  • Wayne Hoff

    This doesn't have to be quite so complicated - it can be done exclusively with Conditional Formatting. I have Excel 2010, so this may not work for earlier versions.

    1. Format the cells to be General. The number of decimal places for some numbers may be very high.
    2. Highlight a cell and click Conditional Formatting. Click New Rule, then "Use a Formula".
    3. Use the formula =(B1-INT(B1))>0 (where B1 is the cell you're formatting)
    4. Click the Format button and choose Custom. Enter [=0]0;.## into the Type field.
    5. Hit Enter several times.
    6. Click on Conditional Formatting again, click Manage Rules, and Edit the rule you just created. Change the range in the "Applies to" field to cover the range you want covered.

    All done.

  • Excellll

    This seems to be the simplest way to have a Decimal shown when there are fractional numbers - and No Decimal shown for whole numbers:

    =TEXT(ROUND(Base_Number,Max_Decimal_Places),"general")
    

    or with cell references:

    =TEXT(ROUND(A1,2),"general")
    

    Gives

    15 = 15
    9.23432234 = 9.23