+ Reply to Thread
Results 1 to 6 of 6

Replace comma on dot

  1. #1
    Registered User
    Join Date
    08-15-2010
    Location
    Poznan
    MS-Off Ver
    Excel 2003
    Posts
    3

    Replace comma on dot

    Hi Folks,

    I have problem with data type.
    I use data in worksheet from url location and all values which are updated come with
    comma separator (polish decimal system use comma instead of dot as separator),
    I use in my excel english decimal separator which is dot.
    Now I would like to replace commas with dots but the problem is that data are constantly
    updating so every time when that happen I have comma in updated data.

    Can anyone advise or help me with macro or function please?

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Replace comma on dot

    You may be able to find a setting in your data source properties which allows you to set that. Otherwise, try referencing your source with =substitute(<cell>,",",".") copied down (you should definitely be able to set your data range properties so this formula is copied down according to the size of the import.

    hth
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Replace comma on dot

    hi makbos...

    Just watch if you need to change also . to ,

    For example: 12.131,22 to 12,131.22

    you'll need spmething like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;".";"@");",";".");"@";",")

  4. #4
    Registered User
    Join Date
    08-15-2010
    Location
    Poznan
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Replace comma on dot

    Hi Cheeky Charlie,

    I don't see anything where I can define data format in data range properties,
    please take a look in attachment.
    About your second solution where I should use this code?
    Attached Images Attached Images

  5. #5
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Replace comma on dot

    Put it in a column next to your data extract copied down the length og the extract (and put a tick in the box on the bottom of the window you posted a picture of).

  6. #6
    Registered User
    Join Date
    08-15-2010
    Location
    Poznan
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Replace comma on dot

    Thanks a lot this help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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