Results 1 to 7 of 7

Thread: problem with numeric format when downloading from external source

  1. #1

    problem with numeric format when downloading from external source



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hello all
    when downloading data to an excel sheet (office 2010) from am external application (SAP) I have some numeric values that I need to sum.
    the problem is that excel does not register the numeric formatting, on many levels.
    the number appears as 0.000,000 (dot is thousand separator and comma is decimal separator)
    even when selecting the cells and changing the format from "general" to numeric, Excel does not register the change.
    I must change the number completely to 0000.00 AND to format the cell to "numeric" for excel to register that it is a numeric value.
    How can I default the cells to be numeric and register the value as a numeric value?

  2. #2
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Go to FILE - OPTIONS - ADVANCED. Uncheck the box for USE SYSTEM SEPARATORS.

  3. #3
    thanks it helped in the sense that I get the error/message that the numbers are store as text: is there a way then to "correct" all the columns in one shot? if I select them and format to numbers is apparently not sufficient and if I select a cell that does not contain the error, the icon helping me to correct disappears

  4. #4
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    I think it shouldn't be hard to convert the text to an actual number. In a new column, use the VALUE function to convert the text number to a number. Then COPY - PASTE SPECIAL - VALUE over top of the original column.

    PLEASE make a copy of your spreadsheet first and practice with the copy until you understand the process and can make it work.

    Hope this helps!
    cbug

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by alisam58 View Post
    is there a way then to "correct" all the columns in one shot?
    Try you use Find/Replace (Ctrl+H) (time about 30 seconds)

    Select only cells with problem

    Find field: dot => Replace field: # => Replace All button
    Find field: comma => Replace field: dot => Replace All button
    Find field: # => Replace field: comma => Replace All button

    Select all cells => Format cells => General
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Candybc i tried but it does not work: what I get is the little triangle on the left top of the cell reporting an error and the error is that the number is stored as text: I can change that but it is over 8000 lines and I am trying to do that by selecting the columns but it does not work.
    Navic: that is what I did in fact but as this is a report that must be regularly downloaded to excel and not a one time thing, I was trying to have this done in an easier way. Is there a Macro I can create for that?

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by alisam58 View Post
    Navic: that is what I did in fact but as this is a report that must be regularly downloaded to excel and not a one time thing, I was trying to have this done in an easier way. Is there a Macro I can create for that?
    Can you attach small sample worksheet (imagination data)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

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