How to determine no. of elements in a semi-colon string in Excel VBA?

Hello,

I have a string in a cell

David McUseless (7000);100;Karen SanFrancisco(190);100;

How do I determine that there are x elements in the above string and extract each pair of string from the string?

In the above string, there are 4 elements.

If there are x elements, I want to divide that by 2 to get the total number of lines to insert below the active row.

Thanks,

If there is always a semi-colon after the last element, you could use the following formula to determine the number of elements:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))

In VBA it would use Len() and Replace(), of course.

You could then divide that by 2 to get the # of lines to insert.

Originally Posted by zwieback89

David McUseless

Thanks for my laugh today. Glad Paul got you worked out.