Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Angry Excel 2013 - Imported text won't convert to numbers

    I am running a report from Microsoft Dynamics CRM. I have exported both as csv and as an xlsx spreadsheet but neither will allow me to convert the figures into 'number' format in Excel.

    I have looked and tried several solutions from the web and microsoft but NONE will convert these characters to number format.

    I have used the paste Special then multiply by a "1";

    Have tried the Data, Convert to numbers method,

    Have used the VALUE function, resulting in a #VALUE result

    I have done TRIM and made certain there are no other characters (eg. $ , . ) in the cells than the figures i want converted

    NOTHING WORKS, except typing over the figures ... which is not a viable solution as there are several hundred cells, and this will be requested regularly.

    I have attached and excerpt from the file if that helps. It is the Est. Revenue column figures I need to convert.

    Any help will be gratefully received. Thank you
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 980 Times in 910 Posts
    Can you also post a sample of the CSV?

    cheers, Paul

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Here's your sample file, with a formula fix in column [G]
    (you can then paste these values back etc etc etc)

    The difficulty arises because you are unable to see the hidden "?" character that follows the "$" sign in column [E] (use a formula to check the LEN of the entries!)

    ..I also removed your hyperlinks in column [A] by using paste Special then multiply by a "1" (and then applying Tahoma-8 format, wrapped text)

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    webmistress (2016-04-18)

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi again

    You have probably worked out by now that you can fix this issue without using any formulas and without using VBA.

    zeddy

  6. #5
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question

    Hi Paul, sorry for the delay have been away from work, unwell.

    Tried to upload the csv but system is saying its an invalid file?
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  7. #6
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much Zeddy,

    that all makes sense, but I'm intrigued .... would you normally check the length of characters when you encounter these problems? I didn't think of doing that.

    Also, how in earth did you detect that there was a "?" character there after the $ (or anywhere for that matter)?? I can see that "LEN" shows 10 characters rather than the 9 that are visible, but how did you find that it was a "?" and where it sits?

    Thanks again for the fix.

    Cheers,
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  8. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 980 Times in 910 Posts
    Rename the file to TXT instead of CSV.
    Instead of uploading the CSV copy a few lines and paste them here.

    cheers, Paul

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Paul

    ..if the file in post#1 was renamed as a .txt as you suggest (ignore any warning messages about changing file extension), you could then just 'drag' this .txt file from the Windows Explorer folder directly onto an already-opened-session of Excel.

    zeddy

  10. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 980 Times in 910 Posts
    I was trying to get the data as CSV as this won't have the formatting information and may prove easier to import than modifying the sheet with formulas. (Always after an alternative.)

    cheers, Paul

Posting Permissions

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