• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding multiple occurrences of a character in a cell

A

Anil Kulkarni

New Member
Hi 'Excel'lencies,

Apols if this has been dealt with earlier but I have a query:
I have a cell with @ 1000+ names separated by a ";". I need to find the number of ";" that occur in the cell. Kindly guide me as to how it can be done.
E.g. Cell has:

FirstName1, LastName1;FirstName2, LastName2;FirstName3, LastName3; and so on.
I need the number of times the character ";" occurs in the cell. Note that all this info is in a single cell. Thanks
 
Hi Kulkarni ,

You are right that this has been asked and answered earlier in this forum.

This is a standard technique ; what you need to do is :

1. Get the number of characters in the cell ; this is done by a formula such as =LEN(A1) , where A1 contains the 1000+ names delimited by the semi-colon.

2. Replace all the delimiter characters , in this case the semi-colon , by a zero-length character "" ; this is done by a formula such as =SUBSTITUTE(A1,";","").

3. Get the number of characters in this reduced-length string ; =LEN(SUBSTITUTE(A1,";",""))

4. Subtract the length in 3 from the length in 1 by : =LEN(A1) - LEN(SUBSTITUTE(A1,";","")) ; this will give you the number of delimiters in A1.

5. The number of items in A1 , in case you need this too , will be 1 more than the value obtained in 4.

Narayan
 
Thanks Narayan. You have explained the strategy well for me to deal with such problems should they occur again. Important to realise that Excel provides the tools thru formulas but the strategy has to be developed individually...Thanks again.
 
Good day Anil Kulkarni

You have your answer, but here is my question...who on earth would put 1000+ names into one cell? As the maximum number of characters in one cell is 32,767 and the maximum displayed in the cell is 1024 (all characters displayed in the formula bar) and with a maximum cell width of 255 characters it must be one hell of a messy spread sheet!
 
You must log in or register to reply here.
Back
Top