+ Reply to Thread
Results 1 to 7 of 7

comma to decimal in a mixed document

  1. #1
    Registered User
    Join Date
    11-11-2019
    Location
    Copenhagen
    MS-Off Ver
    10
    Posts
    4

    Exclamation comma to decimal in a mixed document

    Hello everyone!

    I hope you can help me!!

    I have a data set, that contains a mix of decimal separations in few columns (some numbers are separated with comma and others with a point).

    I have tried different formulas on this forum to correct this, but it doesn't do any good. I have also tried to use the replace option (which has worked on other columns, but not all).

    Would highly appreciate any feedback or help! The data set is hughe (with thousands of rows) and it would be a pain in the butt to change that manually.

    In adbance - huge thank you.

    help.PNG

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: comma to decimal in a mixed document

    If you have a mix of . and , in those cells, they are probably all text, not numbers.

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-11-2019
    Location
    Copenhagen
    MS-Off Ver
    10
    Posts
    4

    Re: comma to decimal in a mixed document

    Hej Ford,

    Thank you for your swift reply.

    Please see an attached excel workbook.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: comma to decimal in a mixed document

    OK, I see that they are actually all real numbers, but they are set to show a thousand-separator. If you set them all to General Format, you will see what I mean - the , goes away.

    Now, you need to decide on what you want to do with this. is (A2) 75,016 supposed to be 76.016, or...?

  5. #5
    Registered User
    Join Date
    11-11-2019
    Location
    Copenhagen
    MS-Off Ver
    10
    Posts
    4

    Re: comma to decimal in a mixed document

    FDibbins,

    Thanks for your help.
    I see what you mean. The thing is that, e.g. A2 should be 75.016. Is there any way to do this without having to manually re-write the whole column?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: comma to decimal in a mixed document

    1 way would be to add 2 helper columns and run a calc there to do the conversion, where needed. Something like...
    C2=A2/IF(INT(A2)=A2,1000,1)
    copied down, and across to the next column.

    You can then either copy/paste values over the orig data, or just hide the orig columns, keep the helpers and use them instead.

  7. #7
    Registered User
    Join Date
    11-11-2019
    Location
    Copenhagen
    MS-Off Ver
    10
    Posts
    4

    Re: comma to decimal in a mixed document

    Thank you so much!
    This helps a lot!

+ 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: 3
    Last Post: 10-07-2018, 10:22 PM
  2. [SOLVED] Using comma as a decimal point
    By sjb72 in forum Excel General
    Replies: 5
    Last Post: 01-04-2018, 07:55 AM
  3. Assistance With Mixed Decimal
    By meppwc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-17-2017, 08:38 AM
  4. [SOLVED] Add a comma without decimal affected?!
    By shimaa01234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2015, 07:11 AM
  5. Replies: 2
    Last Post: 08-19-2013, 06:17 PM
  6. Replies: 4
    Last Post: 09-06-2012, 07:51 AM
  7. Replies: 3
    Last Post: 07-05-2006, 01:57 PM

Tags for this Thread

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