Counting occurences of characters in a cell
Try this:
=IF(ISERR(FIND(",",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,",
","^",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))))
"John" wrote:
Hi,
I've want to be able to return the country in the cells, EG
Luxembourg
London, UK
Miami, FL, USA
I can get this to work using
=IF(ISERROR(FIND(",",P431))=TRUE,P431,TRIM(MID(P43 1,FIND(",",P431)+1,LEN(P431))))
Problem is when i have two ',' i only get Fl, USA where all i want is USA.
I vould possibly use an extra iteration in the function to say if the result
still has a common in then search again, but wanted to know if their is
something in XL that will count the number of ',' in a string?
THanks
John
|