Ask Experts Questions for FREE Help !
Ask
    Menthy's Avatar
    Menthy Posts: 1, Reputation: 1
    New Member
    		  
    #1

    Jun 5, 2011, 10:54 PM
    Changing text format with hyphen on excel 22-Apr-08?
    Hello,

    I've been trying to figure out to change a text format on excel to DD-MM-YY format but no luck so far. I tried almost all the formulas that I know but didn't work out. Can someone please help me as I have a huge list of data that needs to be converted to DD-MM-YY format.

    This is an example of my data: (I'm unable to change the format as there isn't any hyphen signs)
    May 27 2011
    Nov 30 2009

    Desired format>>>
    27-May-2011
    30-Nov-2009

    Can someone please help me with a formula asap.

    Many Thanks in advance!

    Cheryl
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #2

    Jun 6, 2011, 03:50 AM

    Don't use a formula. Go to Formatting and create a Custom date format then apply the format to the cells.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #3

    Jun 7, 2011, 10:16 PM

    Actually, if the cells have the exact strings you posted above, then the real problem is they aren't really dates yet, they are text strings. First, let's use a trick to convert the text into real dates.

    1) Highlight all the dates in a single column
    2) Select Data > Text to Columns > Delimited > Next
    3) Uncheck all the delimiters on page 2
    4) Click Next > Column Data format > Date: MDY > Finish


    On my system this immediately converted your text strings into the "desired format" you wanted, but if they don't on yours, then you can now use Scott's suggestion to apply a date format to those cells for the format you want.

    Repeat with each column of dates, one column at a time.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel 15 digit issue, tried converting to text, text to column feature negates fix [ 6 Answers ]

I have the following numbers that exceed 15 characters that needs to be split into its own columns. Down the road, there would be thousands of such rows of data with the first couple set of unique numbers. 890432453253208820,5004500558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11...

How can I get a 9-digit numeric datapoint to format as text, and link up to text [ 2 Answers ]

How can I get a 9-digit numeric datapoint to format as text, and link up to text? I was able to convert a column of 9-digit Social Security Numbers to 9-digit text, but they don't link up to =if statetments as I try to match them up. Thanks for your help

How to edit the jpg file which is in text format? [ 1 Answers ]

I want to know how to edit jpg file which is in text format... if I convert it into word file than how can I edit that word file... let me know the answer..

Text does not display in readable format [ 2 Answers ]

when trying to access news items from a website by clicking on a title, I get a display of what looks to me like some extended ASCII code: ...

Excel Format [ 7 Answers ]

I need someone help. Everyday I need to format data like the following 1003, 1876, 1952, 2011, 2048, 2057, 2073, 2094, 2111, 2128 This gets pasted into one cell in a spreadsheet. I would like to be able to format it so it goes into the sheet 1003 1876 1952 etc into separate cells. Is this...

View more questions Search