+ Reply to Thread
Results 1 to 13 of 13

de-concatenate

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    de-concatenate

    Hi,

    I just need help for below data. I have the data available for column A,B,& C and I want the result showing in column D&E (see attached file)
    Can someone help me with the formula for D&E. Thanks in advance.

    thanks,
    goya
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    Column E is easy enough:

    =TEXT(B2,"dd/mmm/yy")&" "&TEXT(--LEFT(C2,FIND(" ",C2)-1),"hh:mm:ss")

    But do you need to do further manipulations with this date/time?

    but, D....
    Glenn



  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    Ignore that... use this:

    =B2+(--LEFT(C2,FIND(" ",C2)-1))

    Format as dd/mmm/yyyy hh:mm:ss

  4. #4
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    Re: de-concatenate

    Awesome...this formula works just perfect. Thanks Glenn :-)

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    And a bit of a monster for D

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

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    I should have added that the column D-Monster will work ONLY if the first set of numeric characters (1234) is ENTIRELY numeric. It was in your example. It will fall over with 123F. Will that ever be the case??

  7. #7
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    Re: de-concatenate

    I don't understand the formula...but this is perfect. thanks Glenn...you've just made my day. :-)

  8. #8
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    Re: de-concatenate

    Hi Glenn,

    It's fine. It will always be numeric.

    Thanks,
    goya

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  10. #10
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    Im off for the night. If you want an explanatio , say so amd I will add one in the morning. Only on the phone now.

  11. #11
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    Re: de-concatenate

    Hi Glenn,

    Yes. if you could give me an explanation for the formula...that would be great.

    thanks,goya

  12. #12
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: de-concatenate

    Deep breath....

    TRIM(LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(1:10000)))))

    If you ask LOOKUP to look for a very large number (99 to the power of 99) in an array of numbers, it will return the last number in the array. This formula adds 0123456789 onto the end of the string in A2, so that EVERY digit is present at least once. The SEARCH bit then returns the POSITION in the string of the first occurrence of every digit in turn. The digit with the LOWEST value at this point HAS to be the first digit in the string. STARTING from that position (so that all the leading letters have been eliminated), it assembles a series of stings, being 1, 2, 3, 4, etc, characters long starting from that first digit. Sooner or later, it finds a space and thereafter text or a number, or whatever. From that point onwards, the value returned by the array ceases to be a number, its text consisting of a number and a space. Lookup returns the last real number it sees – which is the value that you want.

    This: =TRIM(RIGHT(SUBSTITUTE(A2,"@",REPT(" ",LEN(A2))),LEN(A2))) returns everything after the last @ sign in a string. Put a space instead of the @ sign, and it will return 117 from A2.

    Next is =SUBSTITUTE(A2, TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),””), which removes the 117 from the end of the string. Now all you need to do is repeat the earlier equation using this (and not A2) as the starting point.

    =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2, TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),””)," ",REPT(" ",LEN(SUBSTITUTE(A2, TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),””)))),LEN(SUBSTITUTE(A2, TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),””))))

    Does just that. You now have the two numbers you need. All that remains is to put in the staring text and the odd hyphen.

  13. #13
    Registered User
    Join Date
    12-09-2015
    Location
    Winnipeg, MB
    MS-Off Ver
    Windows 7
    Posts
    52

    Re: de-concatenate

    Wheew...that's a long explanation too. Thanks...I'll try to understand it if I have time today. Have a good day:-)

+ 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