Returning how many of a character in a cell

Aladin proposed this formula that really works..

If your text is in A1, try with:

in B1 put your "look" text, in this case, "\".
in C1 put this formula

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

Juan Pablo

Juan,
Why do you need the last "/LEN(B1)"? Won't you end up with an error? The formula works without it.
Richard

It's just for cases when B1 isn't a one char string.

For example, remove the /LEN(B1), if i wanted to find in "Juan Pablo" the string "a", the function would return 2, but if i wanted to find "Jua" (Which only appears 1 time), the function would return 3 !, that's why you divide it. And it shouldn't return in an error UNLESS there's nothing to find...

Juan Pablo

Thx for explanation (nt)

For example, remove the /LEN(B1), if i wanted to find in "Juan Pablo" the string "a", the function would return 2, but if i wanted to find "Jua" (Which only appears 1 time), the function would return 3 !, that's why you divide it. And it shouldn't return in an error UNLESS there's nothing to find... : Juan,

Juan, this is most interesting! Question for you...

Do you mind if I put this solution on my website (beats the heck out of the UDF I created to do the same thing)? Of course, I'll give you 100% credit (hate the idea of claiming ownership for something I didn't do).

Sincerely,
BarrieBarrie Davidson

Sure....

but the credit goes first to Alading, (and i think i saw it somewhere in a essay by Bob Umlas)...

Juan Pablo Do you mind if I put this solution on my website (beats the heck out of the UDF I created to do the same thing)? Of course, I'll give you 100% credit (hate the idea of claiming ownership for something I didn't do). Sincerely,

ALADIN,

Do you mind if I post this solution on my website (full credit to you and sorry for not noticing that Juan had mentioned you in his posting)?

Sincerely,
Barrie but the credit goes first to Alading, (and i think i saw it somewhere in a essay by Bob Umlas)...

Barrie Davidson

No problem. however...

check also Juan's note about having seen it "somewhere in a essay by Bob Umlas". If it's the case that Umlas has published a similar formula before me, I'd think you should at least also refer to that publication. It's a fact life that some things are figured out independently by multiple individuals. So becareful.

Regards,

Aladin

========= Do you mind if I post this solution on my website (full credit to you and sorry for not noticing that Juan had mentioned you in his posting)? Sincerely, : but the credit goes first to Alading, (and i think i saw it somewhere in a essay by Bob Umlas)...

Barrie, i found it. It's at http://www.emailoffice.com/excel/arrays-bobumlas.html

Regards, Aladin ========= : Do you mind if I post this solution on my website (full credit to you and sorry for not noticing that Juan had mentioned you in his posting)? : Sincerely,

Thanks Juan (nt)