Counting occurences of characters in a cell

[Guest]

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(P431,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

 

[Pete_UK]

You can use SUBSTITUTE to get rid of the commas, so that (the length
of the original string) minus (the length of the string with the
commas changed to "") will give you the number of commas in the
original.

Hope this helps.

Pete

 

[Dave Peterson]

=TRIM(RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1,",",""))))))

will return an error if no comma was found.

You could hide it with:

=IF(COUNTIF(A1,"*,*")=0,"",TRIM(RIGHT(A1,LEN(A1)
-FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1,",","")))))))

 

[Guest]

Try this:

=IF(ISERR(FIND(",",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,",
","^",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))))