+ Reply to Thread
Results 1 to 5 of 5

Number decimal does not disply as "Text" format

  1. #1
    Registered User
    Join Date
    09-05-2019
    Location
    Rowlett, Texas
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    3

    Number decimal does not disply as "Text" format

    I have a long list of numbers formatted as "General" and some of them have a decimal and the decimal does not display in this format. I want to change formatting to “Text” but when I do the decimal still does not display until I double click in the cell. How can I change a long list of numbers with decimals to “Text” format and have the decimal display without having to double click each cell? This is very time consuming.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Number decimal does not disply as "Text" format

    Helper column

    1. in B1
    =TEXT(A1,"0.00")

    2. Copy then paste values over the top of column A.
    3. Delete column B
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Number decimal does not disply as "Text" format

    Quote Originally Posted by jtlayne1969 View Post
    How can I change a long list of numbers with decimals to "Text" format and have the decimal display without having to double click each cell?
    Select the range of numbers, and use the Text To Columns feature to change the format to Text.

    In my version of Excel (2010), I would click Date > Text To Columns, then in the TTC wizard, click Next, Next, and in the last step, select Text for "Column data format", then click Finish.

    Unfortunately, MSFT might have changed the UI in Office 365 significantly. So you might need to discover how to navigate and use the Text To Columns feature.

    However....


    Quote Originally Posted by jtlayne1969 View Post
    I have a long list of numbers formatted as "General" and some of them have a decimal and the decimal does not display in this format. I want to change formatting to "Text"
    I presume that you mean the numbers have more decimal places (decimal fraction digits after the decimal point).

    Formatting as Text might not be the best solution, if you still intend to reference the cell values as numeric values. The numeric strings might work in arithmetic expressions; but not in comparisons, lookups, etc, at least not with some extra effort.

    Instead, choose the Number format with sufficient decimal places to display the entire decimal fraction?

    The standard Number format will display all numbers with the same number of decimal places. For example, Number with 8 dp will show 1.23000000 and 789.01234567.

    If do not want the trailing zeros, use the Custom format 0.########. But note: the integer 123 will display as 123. -- that is, with the decimal point.

    If that is undesirable, you might consider Condition Formatting. For example, in addition to the Custom cell format 0.########, add the following CF rule to the cell:

    Use the formula: =A1=INT(A1)
    Format: General

    Because of the relative reference (A1 instead of $A$1), the CF rule can be applied to a range of cells.

  4. #4
    Registered User
    Join Date
    09-05-2019
    Location
    Rowlett, Texas
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    3

    Re: Number decimal does not disply as "Text" format

    Thanks joeu2004 for your suggestion. Text to Columns works great. Yeah, these are part numbers and will not be used in any calculations so text is what I need.

  5. #5
    Registered User
    Join Date
    09-05-2019
    Location
    Rowlett, Texas
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    3

    Re: Number decimal does not disply as "Text" format

    Thanks Special-K for your suggestion. Using a function to add decimals to the numbers will not work in this case because some numbers can not have decimals. These number lists are part numbers and adding a decimal to some of them would change the part number.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can't get a userform text box to display a number format, ie "Currency"
    By Gwg1955 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 11-08-2015, 08:40 PM
  2. [SOLVED] Convert Long Part Number List to "Text" Format??
    By TestMailinator in forum Excel General
    Replies: 2
    Last Post: 04-20-2015, 11:01 AM
  3. Date format of just "st" "nd" "rd" and "th" with text included
    By notrandom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2013, 05:45 PM
  4. [SOLVED] Re: macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 07:14 AM
  5. [SOLVED] macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2006, 10:20 AM
  6. [SOLVED] " / " Changing Decimal number format to Fraction on Protected Cell
    By laudie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2005, 10:15 PM
  7. " / " Changing Decimal number format to Fraction on Protected Cell
    By laudie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2005, 09:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1