+ Reply to Thread
Results 1 to 6 of 6

Insert 0 at beginning of field depending on no of characters

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003
    Posts
    17

    Insert 0 at beginning of field depending on no of characters

    Hi
    Example below of the data I have. I need to convert dates into DD.MM.YY (8 chars) for SAP. I receive spreadsheets where the date is dd/mm/yy and I use the replace to convert the "/" to ".", then format this column as TEXT. My problem is some of these sheets have hundreds of records and rather than individually scroll through and look for data that requires the "0", is there a way to insert this. I can custom format that it looks as it has the zero, but it is not in the true data.

    1.10.12 data should be 01.10.12
    11.10.12 this is correct
    3.11.12 data should be 03.11.12
    3.06.12 data should be 03.06.12 etc

    Thanking you
    Regards

    A

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Insert 0 at beginning of field depending on no of characters

    A1 cell
    1.10.12

    B1 cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Insert 0 at beginning of field depending on no of characters

    you can do the whole process using this...
    =TEXT(B19,"dd.mm.yy")

    you may need to swap the dd.mm around depending on your system date, but that will convert the / to a . and add a leading 0
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Insert 0 at beginning of field depending on no of characters

    Oops.. Misread

    Revised Formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Re: Insert 0 at beginning of field depending on no of characters

    Maybe..

    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  6. #6
    Registered User
    Join Date
    12-21-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    69

    Re: Insert 0 at beginning of field depending on no of characters

    If you need "0" always at the beginning, you can try:

    Please Login or Register  to view this content.
    Hope this helps.
    Kiran

+ 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