Extracting numbers from text in Excel for Age ranges and summing

[EVPZ17]

Hello All, I would greatly appreciate if anyone could help me with this problem. I'm trying to take numbers entered into a list of age ranges in each cell on a spreadsheet and then add those numbers of each age range. For example below and what I've already tried that didn't work:

=MID($C$2:$C$250,ROW(INDIRECT("1:" &LEN($C$2:$C$250))), 1) and I also tried below:
=RIGHT($C$2:$C$250, LEN($C$2:$C$250)*MIN(FIND({0,1,2,3,4,5,6,7,8,9} $C$2:$C$250,&"0,1,2,3,4,5,6,7,8,9"})+1)

Below is what is an example of what is in each cell and I want to take each age range and then add the numbers for each age range.

0-3 years old: 4-7 years old: 8-11 years old: 12-15 years old: 1 16-19 years old: 20 years old and over:

<colgroup><col width="135" style="width:101pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="135" style="width:101pt"> </colgroup><tbody>
</tbody>

 

[Grahhhhh]

Re: Extracting numbers from text in Excell for Age ranges and summing

I've read and reread your question a couple of times and I think I'm still confused. I'm going to rewrite out what I think you're asking and present an answer to that, please correct me if I'm wrong.

So within a single cell you have a string that reads one of your examples above e.g. "0-3 years old:".
In your sheet these age ranges have a number afterwards as you've demonstrated in 12-15 years old.
You want to separate that sum of each individual age range from the string.

If this sounds right then try this:

=RIGHT(B3,LEN(B3)-FIND(":",B3))

 

[Grahhhhh]

Re: Extracting numbers from text in Excell for Age ranges and summing

After rereading your question, it looks like you're saying that whole example is in one cell. If you're looking to extract the total number of kids from each age group the following should work, just change the find functions to whatever you're looking to extract:

=NUMBERVALUE(MID(B2,FIND(":",B2,FIND("0-3",B2))+1,FIND("4-7",B2)-(FIND(":",B2,FIND("0-3",B2))+1)))

 

[Scott Huish]

Re: Extracting numbers from text in Excell for Age ranges and summing

If you could separate the values from the text, with Text To Columns delimited on : you could just use a SUMIF for each range.

Excel 2010
	C	D	E	F	G
2	0-3 years old			0-3 years old	0
3	4-7 years old			4-7 years old	5
4	8-11 years old			8-11 years old	0
5	12-15 years old	1		12-15 years old	2
6	16-19 years old			16-19 years old	0
7	20 years old and over			20 years old and over	0
8	0-3 years old
9	4-7 years old	5
10	8-11 years old
11	12-15 years old	1
12	16-19 years old
13	20 years old and over
Sheet1

Cell Formulas
Range	Formula
G2	=SUMIF(C:C,F2,D:D)
G3	=SUMIF(C:C,F3,D:D)
G4	=SUMIF(C:C,F4,D:D)
G5	=SUMIF(C:C,F5,D:D)
G6	=SUMIF(C:C,F6,D:D)
G7	=SUMIF(C:C,F7,D:D)

 

[EVPZ17]

Re: Extracting numbers from text in Excell for Age ranges and summing

Also, If I want it to take a total from each of those age ranges in each cell would I just replace that B2 with B2:B500 for instance. Some of the records may not have anything entered after every age range. It is basically the user enters the number of kids in each age group they have. I coded the forms to enter the information concatenated that way to put in every age range even if only 1 or 2 age ranges have values entered in.

 

[EVPZ17]

Re: Extracting numbers from text in Excell for Age ranges and summing

I tried to modify the code example you gave for what I need and get #Value. Should I put the ":" after the FIND for the age range or is it looking for that character before it looks for the age range?

=NUMBERVALUE(MID(C2,FIND(":",C2,FIND("0-3 years old",C2))+1,FIND(":",C2,FIND("4-7 years old",C2)-(FIND(":",C2,FIND("8-11 years old",C2))+1))))

 

[Scott Huish]

Re: Extracting numbers from text in Excell for Age ranges and summing

NUMBERVALUE is a function for Excel 2013 and higher, what version of Excel are you using?

 

[EVPZ17]

Re: Extracting numbers from text in Excell for Age ranges and summing

I believe it's newer than 2013. ill check thanks

 

[EVPZ17]

Re: Extracting numbers from text in Excell for Age ranges and summing

I tried the below to get the number to the right of "0-3 years old:" and then sum that number in each cell with a number to the right of that text. I thought maybe I could repeat that for each age range and then put that number in a column for sums of each age group. It doesn't work because too few arguments?
=NUMBERVALUE(MID($C$2:$C$250,FIND("0-3 years old:", $C$2:$C$250)),+1)))

 

[EVPZ17]

Re: Extracting numbers from text in Excell for Age ranges and summing

Also, could I possibly use something like a SUMIF function? For instance SUMIF ISNUMBER to the RIGHT of "0-3 years old:"