Sum cells that contain numbers and text

[Manexcel]

I am using Windows 10 with Excel 2019.

I am looking for a formula that will =sum the numbers in cells A12 and A17 - see below. Both contain text and numerical numbers.
Group 1 and Group 2 will have different text and length of text / words
i.e. GroupA could be just one word but GroupB could be 3 or 4 or 5 or 6 words with varying number of chars in each word


If you can assist it would be greatly appreciated and I thank you for your time and consideration.

Sum of cells that contain text and numbers v0.1.xlsx
	A	B	C	D	E	F	G	H	I
1	I am using Windows 10 and Excel 2019
2
3	I am looking for a formula that will =sum the numbers in cells A12 and A17. Both contain text and calculated numbers.
4	GroupA and GroupB are titles and will have different text, number of words and each word word has varying length in chars
5	i.e. GroupA could be just one word but GroupB could be 3 or 4 or 5 or 6 words with varying number of chars in each word
6
7
8	Use Case
9
10	Proposed Attendees:	sum of A12 and a17 which should be 19
11
12	GroupA (3)
13	Name 1	Position 1
14	Name 2	Position 2
15	Name 3	Position 3
16
17	GroupB aaa bbbb ccccc dddddd eeeeeeeee (16)
18	Title 1	First Name 4	Last name 4	1980	title 10	first name 13	last name 13	2010
19	Title 2	First Name 5	Last name 5	1986	title 11	first name 14	last name 14	2013
20	Title 3	First Name 6	Last name 6	1987	title 12	first name 15	last name 15	2014
21	Title 4	First Name 7	Last name 7	1998	title 13	first name 16	last name 16	2015
22	Title 5	First Name 8	Last name 8	2001	title 14	first name 17	last name 17	2016
23	Title 6	First Name 9	Last name 9	2004	title 15	first name 18	last name 18	2017
24	Title 7	First Name 10	Last name 10	2005	title 16	first name 19	last name 19	2018
25	Title 8	First Name 11	Last name 11	2007
26	Title 9	First Name 12	Last name 12	2009
27
Sheet1

Cell Formulas
Range	Formula
A12	A12	="GroupA ("&COUNTA(A13:A15)&")"
A17	A17	="GroupB aaa bbbb ccccc dddddd eeeeeeeee (" &COUNTA(C18:C26,G18:G26)&")"

 

[Snakehips]

@Manexcel Maybe ?

=RIGHT(SUBSTITUTE(SUBSTITUTE(A12,"(","     "),")",""),5)+RIGHT(SUBSTITUTE(SUBSTITUTE(A17,"(","     "),")",""),5)

 

[Peter_SSs]

.. or possibly this?

=SUBSTITUTE(REPLACE(A12,1,FIND("(",A12),""),")","")+SUBSTITUTE(REPLACE(A17,1,FIND("(",A17),""),")","")

 

[Manexcel]

@Manexcel Maybe ?

=RIGHT(SUBSTITUTE(SUBSTITUTE(A12,"(","     "),")",""),5)+RIGHT(SUBSTITUTE(SUBSTITUTE(A17,"(","     "),")",""),5)

Thank you very much for the very quick response. It works perfectly. A big thank you.

 

[Manexcel]

.. or possibly this?

=SUBSTITUTE(REPLACE(A12,1,FIND("(",A12),""),")","")+SUBSTITUTE(REPLACE(A17,1,FIND("(",A17),""),")","")

Thank you very much for the very quick response. It works perfectly. A big thank you.

 

[Peter_SSs]

You're welcome. Glad we could help.

If the number in parentheses is always at the end and less than 10 digits, then this is a bit shorter again.

=MID(SUBSTITUTE(A12,")",""),FIND("(",A12)+1,9)+MID(SUBSTITUTE(A17,")",""),FIND("(",A17)+1,9)