Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Cell Format For Existing Data (2003)

    I have a column with over 3,000 entries that I need to change the format type. It

  2. #2
    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: Change Cell Format For Existing Data (2003)

    Add this to B1:
    <pre>=DATEVALUE(REPLACE(REPLACE(A1,7,0,"/"),5,0,"/"))</pre>

    Copy it down the rows by going to the bottom-right of B1 and when the cursor changes to "+" dbl-click it.

    Then copy col b and paste special - values onto A, then format as desired.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Cell Format For Existing Data (2003)

    Are you going to be trying to perform calculations with the dates - or are you simply trying to change tha appearance of the cell entries?
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Cell Format For Existing Data (2003)

    The following steps should accomplish what you want:

    1- Insert an empty column to the right of column A. In the top cell of this column insert this formula:

    <pre>=MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)
    </pre>


    2- Fill this formula down the column to the last date in column A. You can do this by double clicking on the fill handle for the cell if there are no empty cells in column A.

    3- You should now have dates in column B. Select column B and then select Copy from the Edit menu.

    4- With column B still selected, select 'Paste Special" from the Edit menu. Select Values in the Paste section of the dialog box and then click OK. The formulas in column B should now be replaced with the date values.

    5- You can not delete the original column A.

    6- You can now format the dates into whatever format you want.
    Legare Coleman

Posting Permissions

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