Richie(UK) said:Hi Guys,
Sorry to butt in, but this is weird.
I entered "...test" (three full stops followed by the word test) and copied in the formula. It showed an answer of 0 (nil). I removed the three stops and it still showed 0 (as expected). Then when I re-entered the stops the result shows as 3 (Hooray!).
(Similarly, just doing Len(A1) with the same string gives an answer of 5!)
Aladin - your formula seems to have the same consequences. That is, 0 initially but then the correct count after editing.
Aladin Akyurek said:... is not a char nor a word if not surrounded by a delimiter.
Andrew Poulsom said:Aladin Akyurek said:... is not a char nor a word if not surrounded by a delimiter.
Actually it seems that Excel thinks it is a char (number 133).
Andrew Poulsom said:If I type ...A in cell A1:
LEN(A1) returns 2
LEFT(A1) returns three dots
CODE(LEFT(A1)) returns 133.
If I edit the cell, delete the ... (one keypress) and precede the A with ...
LEN(A1) returns 4
LEFT(A1) returns one dot
CODE(LEFT(A1)) returns 46.
So editing the cell is the only way I know to escape the Ellipsis interpretation.
Incidentally the same thing happens if 3 dots are typed in the middle of the entry or at the end. And 4 dots is 2 characters, 5 dots is 3 characters, but 6 dots is only 2 characters.
Try:
=LEN(A1)-LEN(SUBSTITUTE(A1,"e",""))
where A1 contains your text.
Edit: Must press Refresh more often to avoid echoes. :wink: