Extracting numbers from text and summing

Welcome to the forum Shaw2380.

The easiest way to deal with this would be to not have the information in one cell. You say it's 'concatenated' which implies to me that the information is taken from somewhere else - is this the case, or is it supplied to you in this format?

If there's nothing you can do about the format, then the easiest (relatively speaking ) way to deal with this is to put the numbers for each age group into a separate column, using MID and RIGHT functions.

If you have your information in A2, put these formulae in B2 to G2, to extract the different age groups numbers:

Formula:

Please Login or Register  to view this content.

Short explanation of each part of the formula:
IFERROR(...,0) gives you a 0 if there's nothing entered for that age group.
-- converts the rest of the result from text to a number (it's equivalent to using VALUE(...)
CLEAN strips out the non-printing carriage return which separates the lines in the first cell
MID/RIGHT with FIND with SUBSTITUTE looks up the part of the cell between the first/second/etc colon : and the next age group (if you want more explanation of this bit, just ask)

Drag the formulae down to extract from A3, A4, etc.
You can then simply Sum the numbers.

The attached file shows an example of this working.

Hope that does what you want.

Edit: just saw your post about SEARCH. You're on the right lines thinking about position in the string - that's what the FIND part of the formulae above does (that is, return the position of what it finds). In fact, since FIND is case-sensitive and SEARCH isn't, if there's a chance that you might get data with '4-7 YEARS' instead of '4-7 years' then you should replace the FIND in my formulae with SEARCH.

The answer to your question is basically the explanation of how the MID/RIGHT, SUBSTITUTE, FIND/SEARCH works.

MID is used to find something in the middle of the contents of a cell. The syntax is:
MID( text/cell , number of starting character , number of characters ). For example, if you have 'text123' in A1, the formula MID(A1,4,3) will give you 't12'.
RIGHT does the same but at the right-hand end of a cell. The syntax is:
RIGHT ( text/cell , number of characters ). For example, with the same A1 as above, RIGHT(A1,2) will give you '23'.

That's why MID is used for all the age groups except the last. For the 20+ age group, the number is at the end, so RIGHT works fine for it.
For both of them you need to find the numbers of the positions of various characters.

That's where FIND (case-sensitive) or SEARCH (not case-sensitive) come in. Other than the case-sensitivity, they do the same thing - I'll just refer to FIND from now on.
The syntax is:
FIND ( text to find , in text/cell ) and will return the position of the first character of the 'text to find'. So, with the same 'text123' in A1, FIND("x",A1) will return '3', FIND("xt",A1) will also return '3', FIND("t1",A1) will return '4'.
Obviously (I hope) you can use this to get a starting character number for MID. You can also subtract one FIND result from another to get the number of characters you want.
So, MID(A1, FIND("t1",A1) , FIND("t1",A1)-FIND("e",A1)) becomes MID(A1,4,4-2) then MID(A1,4,2), which gives 't1'.

So, with your text, you could use FIND with a colon and you'd get the first position of the first colon.
Adding one gives you the position of the next character - which is probably a space, but could be the start of the number of 0-3 year-olds.
FIND with the text '4-7 years' gives the position of the '4' from that text. You could then put these together into your first MID:
MID ( A2 , position of colon +1 , position of '4-7 years' – (pos of colon +1) )

I'm sure you've already spotted the problem - how do you find the second colon, third colon, etc. That's where SUBSTITUTE fits in. This is used to Substitute one thing for another. The syntax is SUBSTITUTE( in text/cell , old text , new text , [optional - instance number] ). The old text/new text don't need to be the same length.
So, with 'text123' in A1, SUBSTITUTE(A1,"12","33") will return 'text333', or SUBSTITUTE(A1,"text","something") will give 'something123'.
The optional instance number allows you to replace the second, third, etc instance of something instead of the first one. So if you have 'text123:text456:text789' in A1, whilst the basic SUBSTITUTE(A1,"text","something") would give 'something123:text456:text789', using SUBSTITUTE(A1,"text","something",2) will give 'text123:something456:text789'.

So, to find the second, third, etc instance of something, you first replace it with something you don't have anywhere in your text (^ is often good, or ~ or something like that). You then search for that - in this sort of form:
FIND ( "^" , SUBSTITUTE ( A1 , ":" , "^" , 2 ))
This will replace the second colon with a ^ then return the position of that - which is equivalent to the position of the second colon.
Note that there's no actual replacement - it's just a virtual replacement inside the formula.


Overall, the logic for the MID formula I gave you for 4-7 years old (for example) works something like this:
We need to find the numbers between the second colon (the one after '4-7 years old') and the start of the phrase '8-11 years old'.
The position of the second colon is found by Substitute of the second instance of ":" with "^". +1 gives the position of the character after it - which is the starting character we want for the MID formula.
The number of characters is calculated by finding the position of the first character of '8-11 years' and subtracting the position of the second colon+1 (actually I subtracted the position of the second colon, then subtracted 1, which has the same effect: 10-(7+1) is the same as 10-7-1 ).

Let's look at it using an example. First I'll number the characters:

Please Login or Register  to view this content.

So the second colon is in position 31 and the '8' of '8-11 years' is in position 36. The MID formula is therefore MID(A2,31+1,36-31-1) = MID(A2,32,4) which gives a result of:
44 (with spaces before and after)

As explained in my previous post, CLEAN then removes the non-printing characters to give just '44'.


I know this has been an extremely long explanation , but hopefully it explains:
1. why there are ^ symbols in the formula when they're not in your text (they're used in SUBSTITUTE);
2. why there's no need for a 'higher age group' for the 20+ age group (RIGHT doesn't need to find anything further).

Hope that helps.
If I've just confused things horribly and something still isn't working, please just let me know!