MID: Extracting each number their respective category

Hi guys,

I've been trying to work on MID Formula and I can't seem to figure out how am I going to work on this problem.
I have a series of 12 numbers, separated by comma, in one cell. Each number per order is represented by a category. My goal is to count the total of number categories tagged as 1 in each cell. To give a better perspective on how I wanted to do it, please refer to the attached file.

Hope you guys could help me out on this.
Thanks a lot!
Rekussu

Hi rekussu,

Put this formula in D7 and then pull it right and down.

=MID($B7,2*(COLUMN()-4)+1,1)

See attached.

Hey,

You needed the 1st, 3rd, 5th etc character.
I thought about it a minute and decided the formula would be
2x +1 with increasing values of x starting with zero.

The Column() returns the column the formula is in. You started in Column D So that was the 4th column over.

So 2x + 1 turned into 2 times (Column() - 4) plus 1

I put this formula above into the Mid formula to pick off the 1, 3, 5, etc characters in the string.

thanks again!

Originally Posted by MarvinP

Hey,

You needed the 1st, 3rd, 5th etc character.
I thought about it a minute and decided the formula would be
2x +1 with increasing values of x starting with zero.

The Column() returns the column the formula is in. You started in Column D So that was the 4th column over.

So 2x + 1 turned into 2 times (Column() - 4) plus 1

I put this formula above into the Mid formula to pick off the 1, 3, 5, etc characters in the string.