Counting how many entries you get after preforming a Text-to-Columns operation.

[WassimN]

Hello Marvelous EXCELers,

Here is what I have:
In cell A2 I have a value (numeric), in cell B2 a set of codes (alphabetic) delimited with a comma.

Here is what I need:
I need to find out how many entries will be parsed into individual columns if I perform a Text-To-Columns operation on cell B2, once I find out that number divide the numeric value in A2 by that number (the count of how many individual codes are generated).

Example:
OK so lets say that in A2 I have 123456, and in B2 I have ABC,DEF,GHI when I perform a Text-to-Columns I will end up with ABC in B2, DEF is C2, and GHI in D2. that would mean I have to divide the value in A2 by 3.

What I have tried so far:
I tried using the Excel is Fun Trick # 6 to get the value at the end of the row after the Text-To-Columns but this trick does not appear to work on alphabetic data?!

Any ideas?

 

[Peter_SSs]

Try

=A2/(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)

 

[WassimN]

Thanks Peter_SSs
Wow sometimes you can't see the foreset from the trees. This is a very elegent solution that I did not think about.

Way to Go! Sir.

Thanks.

 

[Peter_SSs]

Thanks Peter_SSs
Wow sometimes you can't see the foreset from the trees. This is a very elegent solution that I did not think about.

Way to Go! Sir.

Thanks.

You are very welcome.