+ Reply to Thread
Results 1 to 10 of 10

Convert DD-MMM to Text String

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Convert DD-MMM to Text String

    In column B I have a date showing as "14-Sep" for example and the format of the cell is "'dd-mmm". What formula can I use to change this so that it is just a text format and displays as "14 - 9". Note the spaces between the hyphen which I want added.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert DD-MMM to Text String

    Something like this:

    =DAY(A1) & " - " & MONTH(A1)

    That result will read as text.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Convert DD-MMM to Text String

    =text(a1,"d - m")
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Convert DD-MMM to Text String

    Thanks all. Both work.

  5. #5
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Convert DD-MMM to Text String

    One more thing actually....so if column B contains only a year, such as 2014 (format is general), the code results in "6 - 7". How can I ensure that if a year on its own appears, that the result is blank?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert DD-MMM to Text String

    Maybe:

    =IF(LEN(A1)=4, "", DAY(A1) & " - " & MONTH(A1))

  7. #7
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Convert DD-MMM to Text String

    Quote Originally Posted by JBeaucaire View Post
    Maybe:

    =IF(LEN(A1)=4, "", DAY(A1) & " - " & MONTH(A1))
    Perfect. Last problem now. The cell can also be for example "Mar-40" in the format "mmm-yy". Anyway to integrate into your previous formula something that would also make this display as text like "03 - 40" ?

  8. #8
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Convert DD-MMM to Text String

    Please Login or Register  to view this content.
    So I've put in other code now and the above list of data is what I now have in column A. THe numbers and months etc can obviously change. What formula do I need to make the data look like:

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Convert DD-MMM to Text String

    Any ideas with the post directly above? I can't imagine a formula would be too complex? Perhaps if anyone knows VBA, it'd be easier that way?

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert DD-MMM to Text String

    Your output requirement in relation to the data seems odd on the surface, inconsistent. Though I do understand it, it just looks like the wrong thing to be doing.

    I would prefer to see an actual workbook with actual examples of this mixed bag of raw data and the desired output in an adjacent cell.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Convert text string to date
    By maacmaac in forum Excel General
    Replies: 5
    Last Post: 11-29-2015, 08:33 PM
  2. How to Convert Text in a String
    By jlutz in forum Excel General
    Replies: 2
    Last Post: 05-21-2015, 06:31 PM
  3. [SOLVED] Convert Text String to Time
    By sal2ahj0y in forum Excel General
    Replies: 2
    Last Post: 05-09-2012, 12:31 PM
  4. Macro to convert the text in to string?
    By mithil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2010, 12:05 PM
  5. Macro to convert the text in to string.
    By mithil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-02-2010, 11:47 AM
  6. Convert text string help!
    By jenn7279 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2007, 10:43 AM
  7. [SOLVED] Convert text string to date
    By AK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2006, 02:30 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1