Ignore text just sum numbers in cell

[Steve7]

In the youtube short

they use this formula =SUM(SUBSTITUTE(C4:C8, "kg",””) +0)

That’s fine if your only using one lot of letters but what if I want to use a whole different array of letters (within the same cell) and later sum up the cells

To me I thought it would be logical for this formula to work but it doesn’t

=SUM(SUBSTITUTE(C4:C8, "**",””) +0)

Replacing the “kg” with “**” so didn’t matter what letters were there and didn’t matter if letters were all different

 

[Fluff]

Hi & welcome to MrExcel.
If the letters are always after the number try

Fluff.xlsm
	A
1
2	12 kg
3	15oz
4	23 yds
5	50
Master

Cell Formulas
Range	Formula
A5	A5	=SUM(--TEXTBEFORE(UPPER(A2:A4),CHAR(SEQUENCE(,26,65))))

 

[Steve7]

Thankyou !!!!

 

[Fluff]

Glad we could help & thanks for the feedback.