How Do I Truncate Text in Cells?

[byepeeps]

Hi,

I have a selection of phrases listed in an excel spreadsheet. Some of them are quite long and I need to shorten them. How do I shorten them to 25 characters in each cell?

Thanks,
Alex

[sweep]

1st 25 characters =LEFT(A1,25)

[byepeeps]

1st 25 characters =LEFT(A1,25)

Thanks sweep that works great. Is there any way to do it so it doesn't cut off the middle of a word? So essentially 25 characters max but only leaving whole words left. Otherwise I'll have to edit the list.

Thanks,
Alex

[zbor]

More info about your data would need..

This perhaps: =LEFT(H32,MIN(25, FIND(" ",H32)-1))

[sweep]

or

=LEFT(A1,MAX(23,FIND(" ",A1,25)))

(glass half full)

[byepeeps]

Thanks for your replies.

zbor: your suggestion cut too much off
sweep: yours was almost there but it left too much and there was 1 error when I ran it over a few cells.

I've attached a dummy sheet. Are you able to have a go with it? I want to truncate column E, leaving a maximum of 25 characters (including spaces) in column F. Again leaving full words would be great.

Thanks so much,
Alex

[zbor]

Here:

=LEFT(E1,MIN(25, FIND(" ",E1,10)-1))

[byepeeps]

Thanks zbor,

If you have time I'd be interested to know what each part of the formula means.

Thanks again,
Alex

[zbor]

Sure...

Formul =LEFT(E1,number) cut number characters from the left of the string in E1.

Now, since you don't want to put manually 25 characters like sweep write in first solution you have do:

MIN(25, FIND(" ",E1,10)-1)

Which will return number where it finds empty place in a word. If it's too long it will return 25 characters.

FIND(" ",E1,10)-1)

Will return first space after 10th caracter.

So

Find space after 10th (red) character :

Yamaha TP4204ACL(space)Standard Timpani Set of 4 (23", 26", 29", 32") with Covers

=LEFT(E1;MIN(25; FIND(" ";E1;10)-1))
=LEFT(E1;MIN(25; 15-1))
=LEFT(E1;MIN(25; 14))
=LEFT(E1;14)
=Yamaha TP4204ACL

[martindwilson]

hmm shouldnt rows 2,5,9 be
Yamaha YBS-62S Pro
Yamaha YSH-411SWC Brass
Yamaha YSH-411WC Brass ?
to get nearest to <=25 try
=IF(OR(LEFT(A1,26)=" ",LEN(TRIM(A1))=LEN(LEFT(TRIM(A1),25))),LEFT(TRIM(A1),25),LEFT(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1)," ","^",25-LEN(SUBSTITUTE(TRIM(LEFT(A1,25))," ",""))))-1))

[byepeeps]

zbor: thanks for that. I think I understand. All a bit complex for me. Not sure what the -1 is for?

martindwilson: You are most definitely right! Thank you for that formula. It must have taken some time to write. I don't know how you do it. Hat off to you sir!

Thanks to all 3 of you because these formulae give me different options for different situations.

[zbor]

Not sure what the -1 is for?

FIND space will return position of space (i.e. 5 in word abcd efgh)

So to take first word out you need 4 character, not 5.

Therefore FIND space and -1 to get first word.