LEN formula - if string less than 3 cut off last letter

Ace71425

Board Regular
Joined
Apr 20, 2015
Messages
130
I'm looking for a formula that takes the string in A1 and if the text is 4 or greater characters I want it to display the first 3...if the text is 3 letters long I want it to display the first 2...if the text is 2 letters long I want it to display only the first character....let me know what that formula would be I'm assuming using left and len. Thanks!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I'm looking for a formula that takes the string in A1 and if the text is 4 or greater characters I want it to display the first 3...if the text is 3 letters long I want it to display the first 2...if the text is 2 letters long I want it to display only the first character....let me know what that formula would be I'm assuming using left and len. Thanks!
This should work even if the cell is blank...

=LEFT(A1,MIN(3,MAX(0,LEN(A1)-1)))

If the cell will never be blank, then this simpler formula can be used...

=LEFT(A6,MIN(3,LEN(A6)-1))
 
Upvote 0
Both those formulas you guys posted isolate the first 3 letters which is great, but if the text is 3 letters it still shows all 3 letters instead of 2 or if the length is 2 it will still give 2....Basically I need the first 3 letters of someones name so if the name is John I need it to say Joh but if somebodys name is Jon I need it to say Jo....
 
Upvote 0
Both those formulas you guys posted isolate the first 3 letters which is great, but if the text is 3 letters it still shows all 3 letters instead of 2 or if the length is 2 it will still give 2....
The only way my two formulas would do that is if there is an non-visible character following the last visible letter. One of these pairs should handle that occurrence, either these...

Never a blank cell: =LEFT(A1,MIN(3,LEN(TRIM(A1))-1))

Possible blank cell: =LEFT(A1,MIN(3,MAX(0,LEN(TRIM(A1))-1)))

or these...

Never a blank cell: =LEFT(A1,MIN(3,LEN(SUBSTITUTE(A1,CHAR(160),""))-1))

Possible blank cell: =LEFT(A1,MIN(3,MAX(0,LEN(SUBSTITUTE(A1,CHAR(160),""))-1)))
 
Upvote 0
Your quote:
I'm looking for a formula that takes the string in A1 and if the text is 4 or greater characters ....

Joh is not greater then 3
 
Last edited:
Upvote 0
Yes if there name is 4 or greater it needs to be 3 letters like joh but if their name is only 3 letters like jon or ann or Bob it needs to be 2 letters instead like jo an or bo
 
Upvote 0
That one is a no go it displays the first 3 if the name is 3 or above and if the name is 2 characters long it comes up with a value error
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top