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
|