Remove Spaces From Imported Dates

  • Experts:


    I use a program that can export data into Excel. For the most part, it works fine . . . except it doesn't correctly handle dates. For some reason, when it pastes a date into excel it includes 8 or 9 (depending if the month is 1 or 2 digits) spaces in front of the date which makes calculations with these dates impossible unless you go into each cell and delete the spaces (or do Find / Replace).


    Is there a way I can create a non-VBA formula that will reference any one of these cells and some \how identify what the date is & then that cell can be the date I can calc on? I.e., A1 is the date with the spaces in front, B2 is the cell that will reference A1 and produce, as the contents of B2, the date in A1. I can then use B2 in calculations.


    Note: the month formats possible are: M/DD/YY (Jan. - Sept.) & MM/DD/YY (Oct. - Dec.). There are no other variations.


    Thanks in advance.

  • Re: Removing Spaces From Cell To Then Calculate On


    Save your data in "text" format, then open it with excel, format columns that are dates as dates

  • Re: Removing Spaces From Cell To Then Calculate On


    Select the dates and go Edit>Replace and Find What push spacebar leave Replace With blank and click Replace All.


    IF the dates are still not TRUE dates, select one of them and then from the Formula Bar copy the unseen character (Ctrl+c) and use the above method BUT by by pasting (Ctrl+v) instead of using the spacebar.

  • Re: Remove Spaces From Imported Dates


    Thanks for the replies gentlemen:


    scid: I tried your method & I believe I might be misunderstanding your instructions. Are you saying to paste the dates (with spaces in front) in an Excel file, then save the file as a .txt file? Not sure how this helps me.


    Averil: your method, on the surface, looks the most appealing. however, the result of the formula +TRIM(A1) does not result in a date that can be calculated with. even if I copy and paste value this cell is not a "date" in excel's logic. is there another step that can be taken?


    Dave: your method appears to work, although I was looking to do with w/o Find / Replace. Still, your method is more efficient that what I was previously using.


    Thanks again for everyone's help. Really, really appreciate taking time out for my problems.


    REF

  • Re: Remove Spaces From Imported Dates


    Actually Averilp, Dates are stored as whole numbers with 1/Jan/1900 being 1, 2/Jan/1900 2 and so on.


    Times are stored as decimals with 1 being uqual to 24hrs.


    Quote

    Dave: your method appears to work, although I was looking to do with w/o Find / Replace

    Not sure why you don't want to use find/replace but it can be automated to become a click action.

  • Re: Remove Spaces From Imported Dates


    Thanks for the correction Dave... I had actually typed a sentance on dates/times as numbers/decimals etc. then decided to just link to your page so cut the majority of it out and left the wrong end part in so jumbled it up - sorry!


    Not sure why there is resistance to find/replace either as it can be done like so (not prettied up just whipped out of a file I've been using):



    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Remove Spaces From Imported Dates


    Dave: I was hoping for a method that would reference the pasted in cells and, with a formula, output the date without the spaces so that it is calculated with. However, a few iterations of Find / Replace made me realize it can be done quickly. So, I'm going with your method.


    Averil: As I mentioned in my original post, I needed a non-VBA method. The reason being is that this is a work product and I don't think my co-workers who might also be using this file at some point are at all familiar with macros. Still, I appreciate your effort.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Averil: you know what, I overlooked your spreadsheet & just now looked at it. it appears to be working. what is interesting is that you cannot re-format the trim formula into another date format (at least I can't), but you can reformat the calculation below. interesting.[hr]*[/hr]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!