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

#### Ace71425

##### Board Regular
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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))

Try this: =IF(LEN(A1)>3,LEFT(A1,LEN(A1)-1),A1)

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....

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)))

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:
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

=IF(LEN(A1)>3,LEFT(A1,3),LEFT(A1,LEN(A1)-1))

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

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
Did you see Message #5 yet?

Replies
10
Views
503
Replies
5
Views
175
Replies
1
Views
95
Replies
4
Views
227
Replies
16
Views
450

1,196,229
Messages
6,014,129
Members
441,804
Latest member
oifaejw

### 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