# LEN Function

#### SonicBoomGolf

##### Active Member
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
BCDE
30CurrentNumberFinalNumber
3193901598469390159846
3293901598469390159846
3393901598469390159846
3493901598469390159846
3594643043079464304307
36939015984629390159846
37939015984629390159846
38882671155388826711553
39882673327758826733277
408826733277528826733277
418826733277538826733277
428826733277548826733277
4326200006169102620000616910
4426200007100692620000710069
4526200370109102620037010910
4626200391909102620039190910
Sheet1

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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Brian from Maui

##### MrExcel MVP
How about just using < 13

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

#### Joe Was

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

#### gaynard_nelson

##### Active Member
trackman69 said:
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

##### Active Member

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

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

#### SonicBoomGolf

##### Active Member
They all worked beautifully. Thanks!!!!

Replies
5
Views
165
Replies
13
Views
214
Replies
0
Views
56
Replies
2
Views
272
Replies
21
Views
305

1,129,809
Messages
5,638,482
Members
417,027
Latest member
wlknspc7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back