I run a small hotel and want to count the number of adults and children that are...

Question 1

I run a small hotel and want to count the number of adults and children that are in the hotel.

Below is the format that I need to have the data in, where both the number of parents and children are in the same cell. To keep things simple, I want to have all the rooms in column A, and the occupant details in column.

The issue is that I cannot find a formula for this. The challenge is that when there are more than 10 children, some formulas then add an adult.

enter image description here

Question 2

Try using the following formula:

enter image description here

=SUM(IFNA(--REGEXEXTRACT(A1:A9, "(\d+)"), 0)) & "a " & 
 SUM(IFNA(--REGEXEXTRACT(A1:A9, "(\d+)(?=c)"), 0)) & "c"

Or,

=SUM(--IFNA(TEXTBEFORE(A1:A9, "a"), 0)) & "a " & 
 SUM(--IFNA(REGEXEXTRACT(TEXTAFTER(A1:A9, " "), "\d+"), 0)) & "c"

Or,

=SUM(--IFNA(TEXTBEFORE(A1:A9, "a"), 0)) & "a " & 
SUM(--IFNA(TEXTBEFORE(TEXTAFTER(A1:A9, " "), "c"), 0)) & "c"

And

=SUM(IFNA(--TEXTBEFORE(B1:B9, "-"), 0)) & "-" & 
 SUM(IFNA(--TEXTAFTER(B1:B9, "-"), 0))

Also, suggested by Reddy Lutonadio Sir:

=LET(adults, SUM(NUMBERVALUE(REGEXEXTRACT(TEXTBEFORE($A$1:$A$9," ",,,0,$A$1:$A$9),"\d+"))),
     children, SUM(NUMBERVALUE(REGEXEXTRACT(TEXTAFTER($A$1:$A$9," ",,,0,"0c"),"\d+"))),
     adults & "a " & children & "c")