+ Reply to Thread
Results 1 to 5 of 5

How to separate text?

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Smile How to separate text?

    Hi All,

    I posted on this forum a couple of weeks ago, and was amazed with how fast and accurate the answers I received were. Thanks again for that.

    Here is my new problem:

    I have a column of entries which read the following:

    UKT_5_070914
    UKT_4.75_070915
    UKT_8_071215
    UKT_4_070916
    UKT_8.75_250817
    UKT_5_070318
    etc.

    I would like to find a formula that returns me in a neighbour column only the number in the middle i.e.

    5
    4.75
    8
    etc.,

    and another column which displays the numbers at the end:


    070914
    070915
    071215
    etc.

    How can I do that?

    Also, the numbers just above actually represent a date: 070914 = 07/09/14. Is there a way to tell excel to convert them in dates?

    Any help on these two tasks is most welcome, thanks to all in advance for your help!

    Andrea
    Last edited by am1221; 10-10-2012 at 05:02 AM.

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

    Re: Simple/quick task (not for me...)

    hi Andrea. assuming your data is in Column A, highlight them & go to Data -> Text to Columns -> Delimited -> Next -> Other: _
    -> Next -> you should see 3 columns in the Data Preview. Select the 3rd column where the dates are. Select Date in format DMY. Destination in B1 if you do not want to overwrite Column A. Then click Finish

    formula-wise, it can be something like this in B1:
    =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",50)),50*COLUMN(A1)+1,50))

    and this in C1:
    =DATE(20&RIGHT(A1,2),MID(TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",50)),50*COLUMN(B1)+1,50)),3,2),LEFT(TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",50)),50*COLUMN(B1)+1,50)),2))

    ps: your title of the thread should be something more meaningful like "How to separate Text"
    Last edited by benishiryo; 10-10-2012 at 04:37 AM. Reason: addtional note to post appropriate title

    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

  3. #3
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: Simple/quick task (not for me...)

    1. Highlight the cells you want to split.

    2. Click on the “Data” tab.

    3. Click “Text to Columns”.

    4. Click “Delimited”.

    5. Click “Next”.

    6. Check the “Space” box. and put your underscore in

    7. Clear all other check boxes.

    8. Click “Next”.

    9. Do whatever else you think is needed.

  4. #4
    Registered User
    Join Date
    09-14-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Simple/quick task (not for me...)

    Amazing, at the beginning it didn't work but after a few trials now it somehow came out just as needed, thanks guys!

    ps. benishiryo, I'll try and put a more meaningful title next time

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Simple/quick task (not for me...)

    am1221,

    It will be good if you change the title so others can benefit from the solutions provided.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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