none
Forcing a numeric string to be formatted like a string: use an apostrophe prefix or something else? RRS feed

  • Question

  • I'm currently working with MapPoint, .NET, and Excel, copying data objects from MapPoint to Excel without any conversion or formatting other than some basic data type checking (data types supported by MapPoint are poorly documented: In the wild, I've seen data types with "ADO" DataTypeEnum values that are not in the MapPoint docs).  

    This usually works very well, although at the moment I have a problem where we have numeric strings which must be kept as-is without any numeric formatting. These are things like account numbers, so they must be kept as-is for display. Of course Excel sees the numeric strings, thinks they are numbers, and displays the larger strings in scientific notation.

    The standard solution is to prefix such strings with an apostrophe. This seems a bit of a kludge. Are there any situations where this can pose problems? Or is that an end user problem? Ie. if they insist on pure numeric account numbers in Excel, then they have to deal with any extra apostrophes?

    I looked at setting format values. Using a format of "0" works well for the account numbers and for non-numeric text using formats, but this leads to integer truncation of floating point numbers. Of course I could check for strings with decimal points and only apply such formats for digit-only strings. Then there will be a problem when a user will has strings holding numbers with decimal points and other maths characters (eg. some telephone numbers)....

    Applying formats could also be programmatically messy: This is because I transfer the data to Excel in one large 2d array. Formats would have to be passed a column (or cell) at a time.


    Richard Marsden
    MapPoint Add-ins at Mapping-Tools.com
    Thursday, April 7, 2011 12:57 AM

Answers

  • Thanks for the suggestions. I think General Formatting is the default - ie. what I've been using. As found, this works for most cases, but Excel does try to be to clever some times (ie. interpreting numeric strings to be numbers).

    The other formats idea is essentially my "0" format idea - it works for many cases, but not necessarily all.

    re. templates: This might be useful for some situations, but not in this case. Columns are essentially dynamic depending on the data present in MapPoint. This is also why a general handling is required rather than specific formatting (eg. use "0" for account numbers might work for fixed data and a template, but I don't know what fields are account numbers). If I need to, formatting can be applied on a column by column basis after the data has been written. A little messy but definitely not impossible.

    I've since found another alternative although I haven't tried it yet: set the data cells to ="string"   If this works I could do this only if the string does not include any single or double quotes, and the strings could be modified before they are written.


    Richard Marsden
    MapPoint Add-ins at Mapping-Tools.com
    • Proposed as answer by Bessie Zhao Thursday, April 14, 2011 8:08 AM
    • Marked as answer by winwaed Thursday, April 14, 2011 11:40 AM
    Thursday, April 7, 2011 1:19 PM

All replies

  • You have really only two choces

    1. Have the cells in General formating.  With cells set to general formating excel tries to detrmine the best formating for a cell type.  Often excel picks a formating which doesn't agree with the formating you would choose.  Also excel checks the format of cells directly above where you are making an entry to help determine what format to use.
    2. Use something other than General formating which turns off the automatic features of excel.  then youare stuck with see all the data using one formating

    Wat sometimes helps is using a template for data entry.  You can preset cells to non general format and then use the template when creating new worksheets.  Or you can copy a worksheet which is format correctly rather than insert a new worksheet.  It is easy to copy a worksheet by simply right click the tab name on the bottom of the worksheet and select copy/move.  Ten check the box saying to make a copy.


    jdweng
    Thursday, April 7, 2011 2:11 AM
  • Thanks for the suggestions. I think General Formatting is the default - ie. what I've been using. As found, this works for most cases, but Excel does try to be to clever some times (ie. interpreting numeric strings to be numbers).

    The other formats idea is essentially my "0" format idea - it works for many cases, but not necessarily all.

    re. templates: This might be useful for some situations, but not in this case. Columns are essentially dynamic depending on the data present in MapPoint. This is also why a general handling is required rather than specific formatting (eg. use "0" for account numbers might work for fixed data and a template, but I don't know what fields are account numbers). If I need to, formatting can be applied on a column by column basis after the data has been written. A little messy but definitely not impossible.

    I've since found another alternative although I haven't tried it yet: set the data cells to ="string"   If this works I could do this only if the string does not include any single or double quotes, and the strings could be modified before they are written.


    Richard Marsden
    MapPoint Add-ins at Mapping-Tools.com
    • Proposed as answer by Bessie Zhao Thursday, April 14, 2011 8:08 AM
    • Marked as answer by winwaed Thursday, April 14, 2011 11:40 AM
    Thursday, April 7, 2011 1:19 PM
  • An update: the ="string" solution appears to be working: It hasn't had a huge amount of testing but the specific customer with the problem says it is working for them.
    Richard Marsden
    MapPoint Add-ins at Mapping-Tools.com
    Thursday, April 14, 2011 11:40 AM