+ Reply to Thread
Results 1 to 17 of 17

Convert Number with comma as decimal separator to a decimal

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Convert Number with comma as decimal separator to a decimal

    Hi, i have an enclosed file which has a column of numbers in it like this

    389,40 and this number should be represented like this 389.40

    i read somehwere online that if you use the CDbl function that it will automatically replace comma with "local decimal" separator.....

    but for me its not.......

    for me 389,40 shows up as 38940.00

    Basically anythoughts on how to convert number from 389,40 to 389.40

    If its just a simple "find/replace" then is there a test or something i can do to make sure i am not replacing a numeric value where a comma exists but its not representing a decimal separator?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Convert Number with comma as decimal separator to a decimal

    With the given data in your file.

    CTRL + H

    Find , (comma)
    Replace by . (dot)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,392

    Re: Convert Number with comma as decimal separator to a decimal

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    Thanks.

    I am using this file in conjunction with a vba program but from your post i gather you're suggesting to simply use a find and replace.

    What i did not mention, and i dont know if this is a possible case, but there could be instances where i have numbers to
    deal with that have commas in them and these commas are NOT decimal seperators and in such cases a simple find replace wont work.

    THe only other thought i had was to simply chk the 3rd character position and if it was a comma replace with a "dot".

    Thoughts?

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    jindon, i get why you do this

    Please Login or Register  to view this content.
    buy why are you doing this

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    ok, i think i see now........the chr 160 code is something i ran into recently actually chr values just before this very numbers i am dealing with now.

    its a space but not a space.....so your just removing them if they exists...right

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    this may be a dumb question but just wanted to clarify something

    if i have a string like this "ABCD"

    the "A" is in the first position.......is this right?

    if so .........did you have the replace function start at the 2nd position?

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Convert Number with comma as decimal separator to a decimal

    if i have a string like this "ABCD"

    the "A" is in the first position.......is this right?

    if so .........did you have the replace function start at the 2nd position?
    A is the first position.

    In that case I would start the replace function at the first position.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,216

    Re: Convert Number with comma as decimal separator to a decimal

    2 is the literal value of the xlPart constant, so it's telling the code that the comma is part of the value, not the whole thing.

    I'm curious as to how you ended up with a mix of number formats?

    Also, FYI, CDbl converts a string to a number based on your computer's regional settings so would not work for you as you presumably use a period as the decimal point.
    Rory
    I drink, and I know things

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    Hi thanks for responding..........

    i am sorry but i dont understand this statement..........

    "2 is the literal value of the xlPart constant, so it's telling the code that the comma is part of the value, not the whole thing"

    i have read it several times but i am not uderstanding something.

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    also, i forgot to mention that trying to adapt Jindons code into my own i am having some issues.

    i think his code will replace all "ChrW(160)" values in the string with "" .....how do i do this if i have a string

    value such as this "temp1 "

    Please Login or Register  to view this content.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,216

    Re: Convert Number with comma as decimal separator to a decimal

    The first three arguments to the Replace method are:
    What (the text to replace)
    Replacement (what to replace it with)
    LookAt - either xlWhole (1), meaning the search must match the entire cell content, or xlPart (2) meaning that the text can be found anywhere in the cells.

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    i thought the 3rd element in the replace vba function was the starting position.

    "Replace ( string1, find, replacement, [start, [count, [compare]]] )"

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,392

    Re: Convert Number with comma as decimal separator to a decimal

    That's a Replace function, not a Range.Replace method.

    See
    https://docs.microsoft.com/ja-jp/off....range.replace

  15. #15
    Registered User
    Join Date
    07-20-2020
    Location
    makkah,KSA
    MS-Off Ver
    2017
    Posts
    1

    Re: Convert Number with comma as decimal separator to a decimal

    thanks Dear ..

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,216

    Re: Convert Number with comma as decimal separator to a decimal

    Also, that would be the fourth argument for the Replace function.

  17. #17
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,438

    Re: Convert Number with comma as decimal separator to a decimal

    thanks all.

    I took what you all gave me and i still need to tryout the one where i use the "range" inside the with but i put together
    the code below from the ideas you all gave me not saying its the most compact or even the best but it seems to work.

    I really wanted somethign that only replaced the comma if it was in the "3rd" position from the right. not sure if this is the
    best way but will go with this for now.......or unless someone tells me this way is not good for some reason.

    thanks!


    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] Convert Decimal number to Decimal Reverse
    By cgolledge72 in forum Excel General
    Replies: 15
    Last Post: 09-25-2019, 08:34 PM
  2. [SOLVED] Autofilter does not work properly - Issue with Comma/Dot decimal separator
    By lucas.lobo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2018, 08:01 AM
  3. [SOLVED] UserForm number pad decimal separator
    By julhs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2017, 12:43 PM
  4. Comma decimal separator with SQL insert
    By Quackerspaniel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2016, 01:03 PM
  5. Replies: 1
    Last Post: 03-28-2013, 12:44 PM
  6. decimal separator is comma, not period
    By lmmohr in forum Excel General
    Replies: 2
    Last Post: 02-13-2012, 02:32 PM
  7. Replies: 1
    Last Post: 09-08-2005, 07:05 PM

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