+ Reply to Thread
Results 1 to 9 of 9

Replace "/" for "." removes 0

  1. #1
    Registered User
    Join Date
    12-21-2012
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    6

    Replace "/" for "." removes 0

    Hello all,

    I was wondering if someone could help.

    I'm working with dates that are formatted as 06/03/2014 (American format, UK location) but I wish to replace the "/" with a "."

    The problem is when I do this via my macro Selection.Replace What:="/", Replacement:="." the result is 6.3.2014 but it's essential that the 0's do not get removed.

    If I manually find and replace the problem does not occur....

    How can I overcome this problem?

    Many thanks
    Last edited by Bacbuk; 02-21-2014 at 07:38 AM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace "/" for "." removes 0

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-21-2012
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    6

    Re: Replace "/" for "." removes 0

    Thank you for your reply but this does not do anything other than bring up a message box???

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace "/" for "." removes 0

    Hi - it was an example of how to replace the "/" with ".", i.e. instead of:
    Please Login or Register  to view this content.
    use
    Please Login or Register  to view this content.
    You have not provided any information on the layout of your data, what you are changing, where it should go, the rest of your code etc. so I just did a message box as I have no "crystal ball" handy.

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace "/" for "." removes 0

    you could perhaps try:
    Please Login or Register  to view this content.
    though.

  6. #6
    Registered User
    Join Date
    12-21-2012
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    6

    Re: Replace "/" for "." removes 0

    Thank you once again but here is my problem now.

    Using selection.numberformat = "dd.mm.yyyy" works, thank you, but as my dates are extracted from an online source where the date format is mm/dd/yyyy so in some cases excel automatically formates these as text and the dates that look '"right" excel formats as date so the formatting will not always work on all the data (mix of date and text)

    ultimately what I seek is a way to convert all the mm/dd/yyyy to dd/mm/yyyy

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace "/" for "." removes 0

    Perhaps something along the lines of:
    Please Login or Register  to view this content.
    If it doesn't work, it would be helpful if you could upload a workbook with some examples of the data (click go advanced and then attachments in the reply.)

  8. #8
    Registered User
    Join Date
    12-21-2012
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    6

    Re: Replace "/" for "." removes 0

    attached for reference
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace "/" for "." removes 0

    The code in post #7 seems to work ok for me, although maybe you want "mm.dd.yyyy" instead of "dd.mm.yyyy"

  10. #10
    Registered User
    Join Date
    12-21-2012
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    6

    Re: Replace "/" for "." removes 0

    could you upload the sheet as I only get an run time error 9 - subscript out of range

    thank you

+ 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. 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. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [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
  4. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  5. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1