Count Delimited Text within a Cell

[JE2BD]

Hi...this is my first post, and I'm a Formula novice with no VBA experience.

I have multiple items (Country Names) in cells exported from another database with what appear to be delimiters (semicolons) that cannot be counted. I want to count the number of items (Countries) separated by the delimiters. Is there a simple way to do this?

Column 1 fields:
TAIWAN, PROVINCE OF CHINA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;
CANADA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;CANADA;
INDIA;BRAZIL;DENMARK;GREECE;POLAND;MOLDOVA, REPUBLIC OF;HUNGARY;UNITED STATES;UNITED KINGDOM;

Result needed in Column 2:
2
6
1
7
9

What is the formula needed to get the Column 2 result?

Thanks for any help!

[Richard Buttrey]

Hi, and welcome to the forum.

I'm assuming that first record is a typo since the delimiter after Taiwan is a comma not a semi colon.

One way in B1 copied down

Please Login or Register  to view this content.

[JE2BD]

Thanks, Richard! It works perfectly, and so much time saved! (Now to try to break it down and understand it myself!)

Yes, the first item was indeed a typo..."Taiwan, Province of China" is a single item, not two.

Thanks for the QUICK save. I looked at the LEN function as an option, but would never have come up with the proper string/syntax in a reasonable time frame...much appreciated!

J

[Richard Buttrey]

Thanks, Richard! It works perfectly, and so much time saved! (Now to try to break it down and understand it myself!)



J

Glad to have been able to help

The SUBSTITUTE() function (not a very well known function) is very useful for stuff like this. By replacing any character in a string with the "" null character you effectively reduce the length of the original string by the number of characters you specify. It's then a simple matter to deduct the length of this reduced string from the length of the original to tell you the number of characters you've replaced.