Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert a character (Excel XP)

    Hello,
    I have just imported a large ASCII file into Excel. One column represents a "date" however the numbers are in a general format. For example, 198, 700, 490, 1299 really represents month & year as in: 1/98, 7/00, 4/90, 12/99 and so forth. How can I add the "/" in the proper place so I can change the format to the date format if need be. I tried find/replace using wildcards but that didn't work for me.

    Thanks
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert a character (Excel XP)

    If the value is in cell A1, use the formula<pre>=LEFT(A1,LEN(A1)-2)&"/"&RIGHT(A1,2)</pre>

    This will return 1/98 etc. If you want a date value, use<pre>=DATE(RIGHT(A1,2),LEFT(A1,LEN(A1)-2),1)</pre>

    HTH

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert a character (Excel XP)

    The following formula put into a cell will convert the contents from A1 and put it into the cell with the formula. Copy this into a blank column and copy it down. Paste-special the values then col A could be eliminated.

    <pre>=DATEVALUE(REPLACE(A1,LEN(A1)-1,0,"/1/"))</pre>


    It uses the first as the day (which is what excel would assume if you just entered the month and year).

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert a character (Excel XP)

    Thank you very much Hans! Worked perfectly and saved me a lot of time and tedious work!

    Keep up the good work!
    Louise

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert a character (Excel XP)

    Thank you sdckapr. This solution works as well!
    You guys are great!
    Louise

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert a character (Excel XP)

    Just for variation...

    =TEXT(A1,"0/00")+0

    Date format the formula cell.
    Microsoft MVP - Excel

Posting Permissions

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