176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51

=IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)

I have this formula attached to this line and the result should be TGH805
instead it comes out TGH6.51 can anyone please explain. Thanks

What values do you have in A63 and I63?

"Arlene" <(E-Mail Removed)> wrote in message
news:A467176F-ECAB-4D24-AC21-(E-Mail Removed)...
> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
>
> =IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)
>
> I have this formula attached to this line and the result should be TGH805
> instead it comes out TGH6.51 can anyone please explain. Thanks

What's in I63 and what's in A63, your example looks strange since it looks
like A63 is to the right of I63?

What does =LEFT(I63,2) return?

also you can try to see if it makes any difference if you use

=IF(LEFT(TRIM(I63),2)="41","TGH"&MID(TRIM(A63),3,5),I63)

--
Regards,

Peo Sjoblom



"Arlene" <(E-Mail Removed)> wrote in message
news:A467176F-ECAB-4D24-AC21-(E-Mail Removed)...
> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
>
> =IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)
>
> I have this formula attached to this line and the result should be TGH805
> instead it comes out TGH6.51 can anyone please explain. Thanks

If the following line is in multiple cells:
176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51

A63 = 176.51
B63 = H083
C63 = 2/1/2007 0:00
D63 = HAUL
E63 = 1081
F63 = 6.5
G63 = P
H63 = 601
I63 = 41805
J63 = TGH6.51

Then it would appear you need to change your formula to reference only I63
and Not A63:
=IF(LEFT(I63,2)="41","TGH"&MID(I63,3,5),I63)

HTH,
Paul

"PCLIVE" <pclive(RemoveThis)@cox.net> wrote in message
news:(E-Mail Removed)...
> What values do you have in A63 and I63?
>
> "Arlene" <(E-Mail Removed)> wrote in message
> news:A467176F-ECAB-4D24-AC21-(E-Mail Removed)...
>> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
>>
>> =IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)
>>
>> I have this formula attached to this line and the result should be TGH805
>> instead it comes out TGH6.51 can anyone please explain. Thanks
>
>

Arlene,

I can't explain what's going wrong because I cant see what is in the cells
your formula refers to but if this helps this is what you formula is doing:-

If the 2 leftmost characters of I63 are 41 then put TGH and 5 characters out
of A63 starting at the 3rd character.

So to get the result you expect

I63 must contain 41xxxxx where x is anything
A63 must contain xx805 where likewise x is anything and there is nothing
after the 5.

Mike


"Arlene" wrote:

> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
>
> =IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)
>
> I have this formula attached to this line and the result should be TGH805
> instead it comes out TGH6.51 can anyone please explain. Thanks

Thanks don't know why I did not realize I had an A instead of an I.

"PCLIVE" wrote:

> If the following line is in multiple cells:
> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
>
> A63 = 176.51
> B63 = H083
> C63 = 2/1/2007 0:00
> D63 = HAUL
> E63 = 1081
> F63 = 6.5
> G63 = P
> H63 = 601
> I63 = 41805
> J63 = TGH6.51
>
> Then it would appear you need to change your formula to reference only I63
> and Not A63:
> =IF(LEFT(I63,2)="41","TGH"&MID(I63,3,5),I63)
>
> HTH,
> Paul
>
> "PCLIVE" <pclive(RemoveThis)@cox.net> wrote in message
> news:(E-Mail Removed)...
> > What values do you have in A63 and I63?
> >
> > "Arlene" <(E-Mail Removed)> wrote in message
> > news:A467176F-ECAB-4D24-AC21-(E-Mail Removed)...
> >> 176.51 H083 02/01/2007 0:00 HAUL 1081 6.5 P 601 41805 H TGH6.51
> >>
> >> =IF(LEFT(I63,2)="41","TGH"&MID(A63,3,5),I63)
> >>
> >> I have this formula attached to this line and the result should be TGH805
> >> instead it comes out TGH6.51 can anyone please explain. Thanks
> >
> >
>
>
>