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

[Ace71425]

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!

 

[Rick Rothstein]

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

 

[My Aswer Is This]

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

 

[Ace71425]

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

 

[Rick Rothstein]

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

 

[My Aswer Is This]

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

 

[Ace71425]

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

 

[Br0ck]

What about...

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

 

[Ace71425]

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

 

[Rick Rothstein]

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?