LEN Function

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
=IF(LEN(P5)=13,P5,IF(OR(LEN(P5)=11,LEN(P5)=12),LEFT(P5,10),""))
 

gaynard_nelson

Active Member
Joined
Dec 4, 2002
Messages
323
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
Joined
Aug 7, 2004
Messages
325

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top