4

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.

player0
  • 124,011
  • 12
  • 67
  • 124
John Grischam
  • 224
  • 1
  • 19

3 Answers3

5

yes, use:

=LEFT(B25; 600)&"..."

update:

=IF(LEN(B25)>600; LEFT(B25; 600)&"..."; B25)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Sorry but this shows three dots even when text is under 600 letters. I want three dots to show only when text stops because is over 600 letters. – John Grischam Jan 09 '21 at 19:30
  • It works. I have a further question. Processing speed is very important for me in his file. Does =LEFT(B25;600) is faster than =IF(LEN(B25)>600; LEFT(B25; 600)&"..."; B25) or is the same thing? I mean i can sacrifice three dots for some speed. – John Grischam Jan 09 '21 at 19:50
  • 1
    @JohnGrischam its the same thing. but if you want speed you should use ARRAYFORMULA instead per-cell-formula (just my assumption ofc. no idea whats your dataset) – player0 Jan 09 '21 at 20:15
2

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

Gerard H. Pille
  • 2,528
  • 1
  • 13
  • 17
0

HERE YOU GO:

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

Murat
  • 34
  • 5