de-concatenate

[goya]

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

[Glenn Kennedy]

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....

[goya]

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

[Glenn Kennedy]

And a bit of a monster for D

="ANA-"&TRIM(LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(1:10000)))))&"-"&TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1))," ",REPT(" ",LEN(TRIM(LEFT(A2,FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1))))),LEN(TRIM(LEFT(A2,FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)))))

[goya]

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

[Glenn Kennedy]

Ignore that... use this:

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

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

[Glenn Kennedy]

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??

[goya]

Hi Glenn,

It's fine. It will always be numeric.

Thanks,
goya

[Glenn Kennedy]

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.

[Glenn Kennedy]

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.

[goya]

Hi Glenn,

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

thanks,goya

[Glenn Kennedy]

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.

[goya]

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