+ Reply to Thread
Results 1 to 5 of 5

Number formatted as a text

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-09-2013
    Location
    City, World
    MS-Off Ver
    Office 365
    Posts
    248

    Number formatted as a text

    Hi

    I entered '10 (apostrophe first then 10) in cell A1. The number went to the left hand side of the cell with green triangle at the upper corner of the cell. When I tried =sum(A1), I got 0.

    Then I changed cell A2 format from general to Text and then entered 10 in cell A2. The number went to the left hand side of the cell but without green triangle. When I tried =sum(A2) I got 10

    What is the difference between these 2 scenarios? Why the first one the sum(A1) gave me 0 and the second one sum(A2) gave me 10?

    Thank you very much

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,690

    Re: Number formatted as a text

    Because in the first instance, A1, you changed the number 10 to text. You can?t sum text. In the second instance you just had a number there.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    04-09-2013
    Location
    City, World
    MS-Off Ver
    Office 365
    Posts
    248

    Re: Number formatted as a text

    Thank you very much for your reply. For the second instance, if I had the number there, wont that change the number to text because the cell is formatted as a text? I have hard time to understand the difference. Thank you very much.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,690

    Re: Number formatted as a text

    when you use the sum function excel will try to sum anything that looks like a number regardless of format. The hashtag (whatever that ' is - apostrophe?) in front of the '10 in A1 will keep that cell as text regardless of the format you pick for the cell. below are two sources.

    https://support.microsoft.com/en-us/...a-23af2c8845d0
    https://www.excelforum.com/excel-gen...dding-why.html

  5. #5
    Forum Contributor
    Join Date
    04-09-2013
    Location
    City, World
    MS-Off Ver
    Office 365
    Posts
    248

    Re: Number formatted as a text

    Thank you very much for your help.

+ 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. Replies: 10
    Last Post: 08-17-2020, 02:33 AM
  2. [SOLVED] Text formatted numbers to number format
    By oneyejack77 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2017, 08:33 PM
  3. Is a cell containing a number really formatted as Text?
    By spshipley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-13-2016, 10:17 AM
  4. Replies: 5
    Last Post: 11-30-2014, 07:56 AM
  5. Extracting numbers and text from inconsistent text/number formatted string
    By Brandivil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 01:46 PM
  6. Text when formatted as number will not Sum
    By Laslo1 in forum Excel General
    Replies: 2
    Last Post: 07-23-2012, 10:35 AM
  7. Convert a number formatted as text to a number in a macro
    By MACRE0 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2005, 09:51 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