+ Reply to Thread
Results 1 to 3 of 3

How to give to space between the numbers and words & changing the date format

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    28

    How to give to space between the numbers and words & changing the date format

    Dear Experts.

    I request you all please let me know how can we give the space for the below continuion numbers in excel sheet by using formula. As data is large it will take longer time to bifurcate manually. Hence kindly help me out.

    01/04/2012TO31/03/2013

    I wanted the above in below manner.

    01/04/2012 TO 31/03/2013.

    and also let me know how can we change the date formate by defualt as DD/MM/YYYY for specified column by using short cut.

    I come come across one short cut which is Cont+Shift+3 , by applying the same changes the entire selcted colomn as date formate as dd-mmm-yy. Here if i wanted the format as DD/MM/YYYY, how can I do that by using short cut ? is there any short cut key for the same ? If we can kindly help me out.

    your kind reply would be appreciated.

    Thanks in advance
    Last edited by Venkata Krishna; 07-02-2013 at 05:44 AM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How to give to space between the numbers and words & changing the date format

    This should do it

    =TEXT(LEFT(A1,10),"dd/mm/yyyy")
    and
    =TEXT(RIGHT(A1,10),"dd/mm/yyyy")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,140

    Re: How to give to space between the numbers and words & changing the date format

    hi Venkata Krishna. try:
    =SUBSTITUTE(A1,"TO"," TO ")

    or press CTRL + H
    Find:
    TO
    Replace with:
    TO
    that's a space, TO, space

    i don't think there's such a shortcut. if you want, you can google on how to record a macro & assign a shortcut key

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1