+ Reply to Thread
Results 1 to 4 of 4

Extract date from text cell & convert in date format by trimming extra spaces

  1. 03-28-2022, 10:23 AM #1
    sanjuss2
    sanjuss2 is offline
    Forum Contributor
    Join Date
    12-03-2014
    Location
    India
    MS-Off Ver
    2016 .......
    Posts
    390

    Lightbulb Extract date from text cell & convert in date format by trimming extra spaces

    Hi all,
    I have following date in cell
    30 Apr 2021 ,Friday

    having space before 30

    I want to convert it in 30/04/2021
    Regards,
    Sanjay SS
    __________________________________________________________________________________________________________________________
    Good Luck !!!
    If you feel I have helped, please click on the star to the left of the post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  2. 03-28-2022, 10:29 AM #2
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Extract date from text cell & convert in date format by trimming extra spaces

    If date is in A1, try:

    =DATEVALUE(LEFT(A1,FIND(",",A1)-1))

    Then format cell as desired.
    Reply With QuoteRegister To Reply
  3. 03-28-2022, 10:30 AM #3
    KOKOSEK
    KOKOSEK is offline
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Extract date from text cell & convert in date format by trimming extra spaces

    A
    B
    1
    		 30 Apr 2021 ,Friday 30/04/2021


    A
    B
    1
    		 30 Apr 2021 ,Friday =TEXT(TRIM(LEFT(A1,FIND(",",A1)-2)),"dd/mm/yyyy")
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Reply With QuoteRegister To Reply
  4. 03-28-2022, 10:49 AM #4
    sanjuss2
    sanjuss2 is offline
    Forum Contributor
    Join Date
    12-03-2014
    Location
    India
    MS-Off Ver
    2016 .......
    Posts
    390

    Re: Extract date from text cell & convert in date format by trimming extra spaces

    Thanks both formulas work perfectly...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extracting a date from a text string and then need to convert to a date format.
    By ldborders in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-03-2021, 03:53 PM
  2. Extract the date from a text field and make it into a date format
    By dkfresh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2021, 11:07 PM
  3. [SOLVED] Convert text date in to a date format excel can read
    By BC TIPPING in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-03-2017, 02:49 AM
  4. [SOLVED] How to convert exported text date to desired date format with excel vba.
    By sktneer in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-03-2013, 01:08 AM
  5. Cannot convert date (which is in text format) to date in numeric format
    By geniuspro in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-19-2012, 09:21 AM
  6. Formula to convert text to a date in a date format
    By mikeburg in forum Excel General
    Replies: 5
    Last Post: 08-24-2011, 09:15 AM
  7. Help: How do I convert a text date into a real date format
    By japorms in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2006, 01:36 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1