MID: Extracting each number their respective category

**rekussu** · 08-29-2011, 11:54 PM

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

**MarvinP** · 08-30-2011, 12:11 AM

Hi rekussu,

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

Please Login or Register  to view this content.

See attached.

**rekussu** · 08-30-2011, 12:17 AM

Thanks a lot! I didn't get how it's done but I gotta study on it

You're the best!

**MarvinP** · 08-30-2011, 12:29 AM

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.

**rekussu** · 08-30-2011, 04:04 AM

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.