+ Reply to Thread
Results 1 to 11 of 11

replace "," comma with "." period

  1. 07-15-2016, 02:07 AM #1
    k1dr0ck
    k1dr0ck is offline
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    replace "," comma with "." period

    hi,

    when i generate an excel file consists of amounts from a hardcopy converted thru an OCR it sometimes read the "." period as "," comma
    so i made a code below that replaces a "," comma with a "." period on the selected cells
    would it be possible to just replace the "," when its position is its the 2nd or 3rd last character in the value of the cell

    for example:
    cell value is 1234,5 when replaced would be 1234.5
    cel value is 1234,56 when replaced would 1234.56

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 07-15-2016, 02:58 AM #2
    Kamboj
    Kamboj is offline
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: replace "," comma with "." period 

    Please Login or Register  to view this content.
    Last edited by Kamboj; 07-15-2016 at 03:03 AM.
    Kamboj
    _________________________________________________________________________________
    Mark the thread as SOLVED if my answer satisfy you.
    Reply With QuoteRegister To Reply
  3. 07-15-2016, 03:23 AM #3
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,908

    Re: replace "," comma with "." period

    No need to use Mid.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 07-15-2016 at 03:26 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  4. 07-15-2016, 03:55 AM #4
    Kamboj
    Kamboj is offline
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: replace "," comma with "." period

    @bakerman2
    Right 2 or 3 will show 2/3 character with (,) not exact (,)
    Reply With QuoteRegister To Reply
  5. 07-18-2016, 11:53 PM #5
    k1dr0ck
    k1dr0ck is offline
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: replace "," comma with "." period

    hi,

    tried both but it gives an error
    the code is in a module in the "PERSONAL.XLSB"

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 07-19-2016, 03:55 AM #6
    Kamboj
    Kamboj is offline
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: replace "," comma with "." period

    use xCell inplace of cell

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 07-19-2016, 04:05 AM #7
    millz
    millz is offline
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: replace "," comma with "." period

    Having "xCell.Select" will kill the For Each loop.
    Please Login or Register  to view this content.
    Last edited by millz; 07-19-2016 at 04:08 AM.
    多么想要告诉你 我好喜欢你
    Reply With QuoteRegister To Reply
  8. 07-19-2016, 04:24 AM #8
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,908

    Re: replace "," comma with "." period

    This one works fine for me.
    Had to put code in file because can't get code posted due to website error.
    Also consider to replace Selection in the code by a clearly defined range.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 07-21-2016, 03:49 AM #9
    k1dr0ck
    k1dr0ck is offline
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: replace "," comma with "." period

    got it to work, thanks for all your help!
    Reply With QuoteRegister To Reply
  10. 10-11-2023, 02:13 PM #10
    jpteynor
    jpteynor is offline
    Registered User
    Join Date
    06-08-2017
    Location
    denver, CO
    MS-Off Ver
    2013
    Posts
    1

    Re: replace "," comma with "." period

    I know this was active a while back but do you have a macro I can run for the opposite issue? I have a lot of numbers that have the format "x.xxx.xx" but need to replace it so it is "x,xxx.xx" do you have anything like that saved down?
    Reply With QuoteRegister To Reply
  11. 10-11-2023, 05:03 PM #11
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,979

    Re: replace "," comma with "." period

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. replace all defined range names in the sheet that start with "Street" to "Road"
    By matrex in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-04-2016, 08:53 AM
  2. [SOLVED] VBA help needed to remove all "/" then replace with "-" from cell "B3"and "B5"
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2014, 02:11 PM
  3. Separate cell which have "Comma" into different row & multiple each row "n" times
    By Arun ebl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2013, 11:22 PM
  4. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Formula returning result of "True" or "False" instead of "1" or "0"
    By AllenMead in forum Excel General
    Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  7. HELP on "left","right","find","len","substitute" functions
    By serene83 in forum Excel General
    Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  8. Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next
    By BCB in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 05-13-2006, 05:02 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1