• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there any excel function or tip, to auto separate the entries in cell in ms excel 2007

J

jraju

Member
Hi,
When i format a cell with time or date, i have to format the cell and also has to type manually the / / or -- format for date or : for time. Is there any tip, or function in excel where in it is possible to auto separate the entries according to chosen format. For eg. when i enter 11212014 it would auto separate like 11/21/2014 or 11-21-2014 and if i enter time 950 or 1320, it would auto enter as 9:50 or 13:20. if the entries are limited to the format of selection.
 
The date one I know. After typing your data, select cells with dates, then go to Data - Text to Columns. In the Wizard, hit 'Next' to get to step 3 of 3 (ignore any settings on first 2). On 3rd screen, select Date, and then MDY from dropdown. Hit Finish to complete.

For the time, you'd have to use a formula. Formula for just time:
=TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0)

If you're going to formulas anyway, a more robust formula to cover both dates and time:
=IF(LEN(A2)>4,DATE(RIGHT(A2,4),LEFT(A2,LEN(A2)-6),LEFT(RIGHT(A2,6),2)),TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0))
with a custom number format of:
[>1000]mm/dd/yyyy;h:mm AM/PM
 
No, i want to enter the date and excel auto separate the entries to the chosen format in the same cell. Is there any tip or function
 
In short, no.
You can either learn how to properly input data, or you can setup a formula or something to manipulate the data after you input.
 
Hi,Luke thank you for informing no function
When i enter the formula after i input dates and time , i get num error. What should i input if i have to get the input 12/20/2014 05:35 pm in A2 to get in some other cell?
what should i type in a2 to get the date only in custom format?
What should i type in a2 to get the time only?
 
The formula I gave you before was to handle cases where you are either typing a 4 digit number (time) or a 6+ digit number (date). If you only want the date/time, why input both? Are you really inputting data, or are you converting data that's been given to you?
 
Since the custom format includes time format, i tried that. When you could format the cells with date and time with excel, i had the doubt, whether excel has some inbuilt function to this one. I found in one of the programs, that it has //separators, and you need to give only the 8 digits, it auto split itself to date. So, I asked this question. Chandoo org developed so much formulas and functions to ease the problem faced by users. and hence i asked for shortcut.
Imagine to type date format // for so much cells, even after formatting it with date or time. Excel has to have easy way to solve this issue. If simple macro could be developed as button for date and time the time wasted in date and time input could be saved.
I will try as per your suggestion. How to custom format the date alone and time alone.Your robust formula has both date and time.
I would also be thankful to you if you could say what the custom format reveals so that newbie could improve further in custom formatting
 
Hi, Luke M,
When i tried with separate date and time in A2 it has worked wonderfully. Thank you. Could i expect a reply on what the format you have given is doing.
 
Hi jraju,
I've gotten a bit confused about what all your data looks like. Could you upload a workbook with two columns of data, first column showing what you want to input, second column showing what you want to see/format input into?

PS. Happy 60th birthday! :)
 
Last edited:
Hi, I have informed that the solution given by you has worked. I want to know a little more about the custom formatting of [>1000]mm/dd/yyyy;h:mm AM/PM does. I want to know what this denotes . [>1000]. If person like you say something more about what this means, the users of forum would immensely benefit from it. I know custom formatting of dd/mm/yyyy, but do not know the addition of [>1000]. What does that this does, so that i could also improve my custom formatting style.
 
You must log in or register to reply here.
Back
Top