+ Reply to Thread
Results 1 to 6 of 6

problem with replacing texts that are like dates .....

  1. #1
    Registered User
    Join Date
    01-05-2014
    Location
    ir
    MS-Off Ver
    Excel 2013
    Posts
    3

    problem with replacing texts that are like dates .....

    the problem arises when you're replacing a text with another text that excel thinks of as date, like 1/1.
    you cant use format button in replace dialog-box because it first converts the text to date and then formats it as text that results in a strange number like 41640.
    You can off course use formula but it is not always handy to do it.
    I was wondering if anyone has any solutions for this?
    sincerely...

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: problem with replacing texts that are like dates .....

    41640 is how excel sees a date. Just format as a date to see 01/01/2014

  3. #3
    Registered User
    Join Date
    01-05-2014
    Location
    ir
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: problem with replacing texts that are like dates .....

    it's not the date that i want. it is the text.
    For example you we have a column of numbers and we want to add "/1" to them, so if it was originally "1", it would change to "1/1". And when you do so, you will not get "1/1" but "1-jan".
    it is "1/1" that we need not the date.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: problem with replacing texts that are like dates .....

    Try something like =A1&"/1"

  5. #5
    Registered User
    Join Date
    01-05-2014
    Location
    ir
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: problem with replacing texts that are like dates .....

    that works too but I found a better way.
    if you put an apostrophe before a string, it forces excel to treat it as text, hence keeping it the way it was...
    For above question, I used '1/1 instead of 1/1. and ' is not a part of cells's value...

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: problem with replacing texts that are like dates .....

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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] problem with replacing dates
    By runneer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2013, 09:10 AM
  2. [SOLVED] Replacing 00/01/1900 dates with blanks
    By robertguy in forum Excel General
    Replies: 4
    Last Post: 09-10-2013, 06:13 PM
  3. Replacing texts and cases
    By tsgucci in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-29-2011, 03:39 PM
  4. Filtering/Replacing Problem
    By ranng in forum Excel General
    Replies: 4
    Last Post: 02-14-2010, 01:05 PM
  5. Pasting (replacing) problem
    By berti in forum Excel General
    Replies: 4
    Last Post: 12-13-2005, 06:38 AM

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