+ Reply to Thread
Results 1 to 6 of 6

Macro for Convert DOT to Comma and Comma to DOT

  1. #1
    Registered User
    Join Date
    10-05-2017
    Location
    Bangalore
    MS-Off Ver
    Karnataka
    Posts
    49

    Macro for Convert DOT to Comma and Comma to DOT

    Dear Friends,

    Need help from you, i am trying to convert Dot to comma and when i covert the value decimal points are not appearing example attached and given below as well.

    221.333 is the value and when i convert the . to comma i am getting 221,33 i need the 3rd decimal as well.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-05-2017
    Location
    Bangalore
    MS-Off Ver
    Karnataka
    Posts
    49

    Re: Macro for Convert DOT to Comma and Comma to DOT

    Sorry friends only when the third decimal point is Zero then when i convert . to , third decimal value is not coming like this

    Input : 221.330 Output should be 221,330 but i am getting 221,33

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Macro for Convert DOT to Comma and Comma to DOT

    When the macro begins, the value in B4 is the number 233.91 (displayed with the number format "0.000", so the value displays as 233.910). At this point, be very clear that the value in the cell and what you see in the display are not exactly the same thing.

    The first .Replace method does nothing, because there are no commas in the value (233.91).
    The second .Replace method replaces the "." with "|dot|", so the value changes to the text string "233|dot|91". Note that there is nothing up to this point that changes the value to the text string "233.910"
    The third .Replace method does nothing, because there were no commas in the original numeric value.
    The fourth .Replace method finishes the job by replace "|dot|" with ",", resulting in the text string "233,91".

    My first question would be -- for the purposes of your project, what is the difference between 233.91, 233.910, 233,91, and 233,910 (besides the obvious trailing 0)? To me, these are four different ways of showing the exact same number. For your purposes, how does the trailing 0 actually change the value?

    Since Excel cannot use both "." and "," as the decimal separator in a single spreadsheet, the value 233.91 can be a number or a text string, but the value 233,91 will need to always be text (switch when the default decimal separator is ","). Some of the question here is exactly how do these values fit into the overall project? Are they always intended to be numbers (not text)? Is Excel even going to use these numbers, or are they intended for export (as text) to some other application?

    One possible solution (assuming it fits into the overall project) would be to store the initial value as a text string rather than a number. If I enter '233.910 into B4 (note the leading apostrophe that tells Excel that this is a text string and not a number), then I can run the code and it will return the text string 233,910. Would that work for you, or does that interfere with these values needing to be actual numbers?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    10-05-2017
    Location
    Bangalore
    MS-Off Ver
    Karnataka
    Posts
    49

    Re: Macro for Convert DOT to Comma and Comma to DOT

    Dear Friend,

    Thanks for your valuable time for me.

    In our local website they made like that, they consider dot as separator but in India we are considering comma is a separator.

    So as per them 221.310 when i missing the zero after converting there is a big value missing 221,31. the output should be 2 lak 21 thousand but when i miss the zero its coming only 22 thousand.

    I am ok with last one you mentioned could you please give me the code how to fix it.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Macro for Convert DOT to Comma and Comma to DOT

    For that last suggestion, I did not change any code. The code you have works just fine for that. All I changed was the leading apostrophe upon entering the data so that Excel would see it as a text string and not a number.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro for Convert DOT to Comma and Comma to DOT

    Is this what you want?
    Please Login or Register  to view this content.

+ 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. [SOLVED] Excel Macro to Convert Number Format into 'Comma Style' accross All Worksheets
    By arnel_10 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2017, 05:01 AM
  2. Comma delimited numbers with space after comma
    By Cicada in forum Excel General
    Replies: 5
    Last Post: 04-23-2015, 09:32 AM
  3. [SOLVED] Code to Replace Comma with dot and dot with comma
    By BS Singh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2014, 06:10 PM
  4. Find and remove all text to the left of last comma (including comma) in a cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-05-2013, 08:47 AM
  5. [SOLVED] If comma present, then place a character before and after comma (FORMULA)
    By jaimealvarez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2012, 04:04 PM
  6. replace the comma separator to a dot and dot to a comma at the same time
    By barkiny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2010, 05:42 PM
  7. Macro Convert Row Value to Column Value with Comma
    By Kenji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2010, 04:18 AM

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