'Google spreadsheet left formula with ellipsis

I'm using importxml to import some data from an other website. This results in some cells to show long text. I'm using =LEFT formula to limit the number of characters in a cell but words stop very abruptly and i was wondering if i can add ellipsis ... (three dots which mean "text continues") to my formula. Something like =LEFT(B25;600) ... which should result in "word word w..." Is this possible?

I tried adding three dots in next cell but they show even when text is not shortened so it does not make sense.

if-statementgoogle-sheetsconcatenationgoogle-sheets-formulatextjoin


Solution 1:[1]

I would only add the ellipsis when there are more than 600 characters

=IF(LEN(A1)>600;CONCATENATE(LEFT(A1;600);"...");A1)

This formula is from LibreOffice Calc

Solution 2:[2]

HERE YOU GO:

=IF(LEN(B25)>600,REPLACE(B25, 600,LEN(B25),"..."),B25)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gerard H. Pille
Solution 2 Murat

Related Questions