Bad result
 

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

Bad result
 

What values do you have in A63 and I63?

"Arlene" wrote in message
...
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

Bad result
 

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" wrote in message
...
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

Bad result
 

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" wrote in message
...
What values do you have in A63 and I63?

"Arlene" wrote in message
...
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

Bad result
 

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

Bad result
 

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" wrote in message
...
What values do you have in A63 and I63?

"Arlene" wrote in message
...
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