Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Automatic addition of year to NON-date values

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have an xls with a number of values in the format '...X-Y...'. e.g. '...3-6...'. After I execute a search/replace to clean up my data and remove all the dots (.) excel always adds '2002' to these values and converts them to dates. I then lose my original values.

    I've specified that all values should be text, but the search/replace seems to ignore this and still make them dates.

    Is there any way to turn off excel's automatic date creation and leave my data as is. *So* annoying. I've tried to disable all AutoCorrect crap but still no luck.

    Thanks-
    Mike

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 14:05, mhardy wrote:
    I have an xls with a number of values in the format '...X-Y...'. e.g. '...3-6...'. After I execute a search/replace to clean up my data and remove all the dots (.) excel always adds '2002' to these values and converts them to dates. I then lose my original values.

    I've specified that all values should be text, but the search/replace seems to ignore this and still make them dates.

    Is there any way to turn off excel's automatic date creation and leave my data as is. *So* annoying. I've tried to disable all AutoCorrect crap but still no luck.

    Thanks-
    Mike
    Make sure your cells are formatted as text before replacing. I'll bet they are currently formatted as General.

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's the real kicker. The entire sheet is formatted Text. I've even tried a new sheet with only one cell containing: ...3-6..., formatted as Text, then search/replace '.', with nothing. Replace All, then bang - 3/6/2002. *argh*.

    -Mike

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 14:53, mhardy wrote:
    That's the real kicker. The entire sheet is formatted Text. I've even tried a new sheet with only one cell containing: ...3-6..., formatted as Text, then search/replace '.', with nothing. Replace All, then bang - 3/6/2002. *argh*.

    -Mike
    Hmmm....how about a formula approach? In an adjacent column, use the SUBSTITUTE function and then paste that value over your old data. For example,
    =SUBSTITUTE(A1,"...","")

    This, for sure, will return a string value.

    What do you think?


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use the Data | Text to Columns... menu command.

    1. Select a column of your data.
    2. Choose the Data | Text to Columns... menu command and specify "Delimited" at Step 1 of 3
    3. At Step 2 of 3 check "Other" delimiter, enter "." in the "Other" input field, and check "Treat consecutive delimiters as one"
    4. You have now specified 2 columns. At Step 3 of 3 the 1st column should be assigned "Do not import column (Skip)" as its data format and the 2nd should be set to "Text".

    That should do it!!

    [ This Message was edited by: Mark W. on 2002-03-06 15:31 ]

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, that's way better! Too bad I didn't read the question as posed (haste makes waste).

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks - that did do the trick. Unfortunately I have 12 pages, 10 columns per page and Text to Column only works 1 column at a time. However, that did spark an idea. I've exported as a .csv, search/replaced all my dots out, then re-imported as Text. A little quicker but I still have to do it 12 times.

    Sure seems like a lot of work when search/replace gets me 99% of the way there. I'm surprised there's not a way to turn this 'feature' off.

    Thanks for the quick help!
    Mike


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •