Logical Test on a Substring in a Text

Thank you for your help! All three of you. But it didn't work because of an error of mine in framing the question. My apologies to all. I wanted to simplify the problem, but I am afraid I overly did so. Let me try this again.

Below in Col A is the full text I have to work with in all its glory, "95D55· A6 0187350 02 SUNY·· SCA BAY L -2AGNOR.06 315 32:40-". Its components are 95D55 = Truck number, A6 = Fleet, 0187350 = Trip number, "SUNY" = Customer, "SCA" = Origin of trip, "BAY" = Destination of same, "L" = Trip is loaded, "-2AGNOR.06" = Various trip conditions of load, "315" = miles left, and "32:40-" = the time running ahead or behind.

This Order NEVER VARIES. Its the one of the two fixed characteristics in this text string. The second are the spaces between each pieces of information. The report even adds "dot" after some of the text to be sure the structure of spaces stays the same. Both the order of information and structure of the sentence is fixed and doesn't change.

As already mentioned the "-" indicates if a load is running ahead or on time. The lack of "-" indicates load is late. I guess what I need is more complicated, maybe than I thought. If I had to restate in words the formula or series of combinded functions I need it would sound something like this"

"if(the last 6 characters of A1 equals contains "-", return "32hrs and 40 min ahead",if the last 6 characters of A1 does not contains "-", return 32hrs 40min late)

I hope this explains it better.


Col A

Col A	Col B
95D55· A6 0187350 02 SUNY·· SCA BAY L -2AGNOR.06 315 32:40-

you can use this formula:
=TEXT(--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)),"[h]"" hours ""mm") & IF(RIGHT(A1,1)="-"," mins early"," mins late")

or... =TEXT(--TRIM(RIGHT(A1,6)),"[h]"" hrs ""mm") & IF(RIGHT(A1,1)="-"," mins early"," mins late")

Phucam and Ron,

Thank you it worked!! Like a champ!! Greatly appreciated. But one small problem came up. You will see in cell B2 that "#VALUE!" came up. This occurred because this load is not assigned a truck at this moment. This is known from both the "NO_DRV" substring and there is no time text.

Final question (I hope) is there way to modify the formula in B2 so that if it were not assigned it just returns the phrase "Not Tracking" rather than #VALUE!" error??

If not, I can more than live with it. Thanks again for your help!!

Col A	Col B
96787 A2 0187360 01 SUNY·· ECR BAY L -3AGNRV.09 235 14:40-	=TEXT(--TRIM(RIGHT(A2,6)),"[h]"" hrs ""mm") & IF(RIGHT(A2,1)="-"," mins early"," mins late")
NO_DRV ·· 0192150 01 SUNY·· ECR BAY K 0	Formula above returns "VALUE!"

Thanks again for you patience and help.

Both of the above formulas returned the value "late". At the end of the original text there are 6 spaces that, although show no text, actually exist. One of the things about the computer system we use is that these text strings ALWAYS have the same format and ALWAYS maintain the same structure. So even though there is no time at the end of the text the system maintains 6 spaces for it plus one space to seperate it from the next substring. In this case its "0". Imagine 0 with a space after it and six invisible letters.

Its one of the frustrating things working with our computer system.

Did I miss something? Again Thanks for the help!

Original Text	Formula	Result
NO_DRV ·· 0194170 01 SUNY·· ECR BAY K 0	=IFERROR(TEXT(TRIM(RIGHT(A23,6)),"[h]""hrs ""m""min """)&IF(RIGHT(A23,1)="-","ahead","late"),"Not tracking")	late

Try again:
=IF(ISNUMBER(--TRIM(RIGHT(A23,6))),TEXT(TRIM(RIGHT(A23,6)),"[h]""hrs ""m""min """)&IF(RIGHT(A23,1)="-","ahead","late"),"Not tracking")

or try:
=IFERROR(TEXT(--TRIM(RIGHT(A23,6)),"[h]""hrs ""m""min """)&IF(RIGHT(A23,1)="-","ahead","late"),"Not tracking")