Counting occurences of characters in a cell
 

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

Counting occurences of characters in a cell
 

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

On Apr 27, 1:54 pm, 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(P4*31))))

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

Counting occurences of characters in a cell
 

=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,",","")))))))



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

--

Dave Peterson

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