SUM only numbers in a cell that also contains text.

[Palacemad]

Hi, I'm sure this will be fairly straightforward for someone here...

I have a group of cells that I want adding together, they are cells G65, K65, O65 and S65.

Each one of these cells contains both text and numbers and I only want to add the number part of the cell. They all contain the same formatting, they say the word Achieved, have one space and then the number value.

I have looked at using SUMIF and ISNUMBER but all to no avail.

I would be very grateful if someone can point me in the right direction!

Thank you

 

[Finalfight40]

As the cells are all going to be formatted the same, you can try something like the below:

=SUM(RIGHT(G65,LEN(G65)-SEARCH(" ",G65)),RIGHT(K65,LEN(K65)-SEARCH(" ",K65)), RIGHT(O65,LEN(O65)-SEARCH(" ",O65)), RIGHT(S65,LEN(S65)-SEARCH(" ",S65)))

 

[Finalfight40]

Or:

=SUM(SUBSTITUTE(G65, "Achieved", ""), SUBSTITUTE(K65, "Achieved", ""), SUBSTITUTE(O65, "Achieved", ""), SUBSTITUTE(S65, "Achieved", ""))

 

[Palacemad]

Thank you! Th efforts option definitely works. Will try the second suggestion too as that appears to be more straightforward to understand what is going on.

 

[Finalfight40]

Happy it works for you.

The first option is just looking to the right of the space of each cell since you have a space before each number and the second option is just replacing the "Achieved" with nothing which leaves just the number.

Added the second option since i thought it looks a lot easier to change if needed.

 

[Palacemad]

That's great, thank you again

 

[jasonb75]

How about

=SUMPRODUCT(--MID(CHOOSE({1,2,3,4},G65,K65,O65,S65),10,100))