Bad result

[Guest]

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

 

[PCLIVE]

What values do you have in A63 and I63?

 

[Peo Sjoblom]

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)

 

[PCLIVE]

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

 

[Guest]

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

 

[Guest]

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