LEN Function

[SonicBoomGolf]

I am looking to use a "text size range" in a LEN function, but I don't know how to do it. I want to look at a SKU, and if the SKU is either 11 or 12 digits long, I want the first 10 digits from the left returned. If the number is 13 digits long then I want the formula to return the full 13 digit number. This formula was my attempt at this idea.

=IF(LEN(P5)>10<13,LEFT(P5,10),"")

Excel accepts this formula, but it isn't doing what I am looking for. Attached is a sample data set and the end result I am looking for.

FY05 OS&D YTD (Cut Data) Week Ending 1-3-05.xls
	B	C	D	E
30	CurrentNumber	FinalNumber
31	9390159846	9390159846
32	9390159846	9390159846
33	9390159846	9390159846
34	9390159846	9390159846
35	9464304307	9464304307
36	93901598462	9390159846
37	93901598462	9390159846
38	88267115538	8826711553
39	88267332775	8826733277
40	882673327752	8826733277
41	882673327753	8826733277
42	882673327754	8826733277
43	2620000616910	2620000616910
44	2620000710069	2620000710069
45	2620037010910	2620037010910
46	2620039190910	2620039190910
Sheet1

In the end I want all the numbers to be either 10 digits or 13 digits long. Any ideas?

 

[Brian from Maui]

How about just using < 13

=IF(LEN(P5) < 13,LEFT(P5,10),"")

 

[Joe Was]

=IF(LEN(P5)=13,P5,IF(OR(LEN(P5)=11,LEN(P5)=12),LEFT(P5,10),""))

 

[gaynard_nelson]

I am looking to use a "text size range" in a LEN function, but I don't know how to do it. I want to look at a SKU, and if the SKU is either 11 or 12 digits long, I want the first 10 digits from the left returned. If the number is 13 digits long then I want the formula to return the full 13 digit number. This formula was my attempt at this idea.

=IF(LEN(P5)>10<13,LEFT(P5,10),"")

This formula will do what you want.
=IF(AND(LEN(A24>10),LEN(A24<13)),LEFT(A24,10),"")

 

[SonicBoomGolf]

I have a bunch of 5 digit numbers, but I think that I could get around it with this. Thanks.

P.S. That resopnse was for Brian. Looks like you guys are too quick for me. I'll try them out and let you know how they go.

 

[IML]

Try
=LEFT(A1,10+(LEN(A1)>12)*3)

 

[SonicBoomGolf]

They all worked beautifully. Thanks!!!!