Forum Discussion
Function Error
=SEARCH(".","a…a") gives me an error whereas the correct answer should be 2
Its in one excel cell
second argument in the formula "a…a" doesn’t actually contain a period (.) — it contains a horizontal ellipsis character (…, Unicode U+2026). Visually, it looks similar to three dots, but Excel treats it as a completely different character, so SEARCH(".", "a…a") can’t find a match and returns an error.
You can try
=SEARCH("…","a…a") , this will return 2.
or
=SEARCH(".", SUBSTITUTE(A1, "…", "."))
Please mark as resolved if this answers your question.
3 Replies
second argument in the formula "a…a" doesn’t actually contain a period (.) — it contains a horizontal ellipsis character (…, Unicode U+2026). Visually, it looks similar to three dots, but Excel treats it as a completely different character, so SEARCH(".", "a…a") can’t find a match and returns an error.
You can try
=SEARCH("…","a…a") , this will return 2.
or
=SEARCH(".", SUBSTITUTE(A1, "…", "."))
Please mark as resolved if this answers your question.
Thank You... Be nice if the Office help info for the Search function would mention these things! Crazy.
