Selective sum of numbers in a string

[rahulajmera]

Hello,

I need some help in selectively summing of numbers in a string cell in MS Excel.

For Eg:
If the string is "The House located at 7th Ave, New York, NY 10036, USA has the following dimensions: Carpet Area of 50 sqm or 550 sqft, Balcony Area of 10 sqm or 110 sqft, Terrace Area of 20 sqm or 220 sqft"

I want to selectively sum all the numbers that precede "sqm"
That means I want the SUM = 50 + 10 + 20 = 80.
How can we do that in excel?

I have an excel containing hundreds of such string cells and its manually too cumbersome to sum.

 

[Anthony47]

Yes.

The string looks like:

The House located at 7th Ave, New York, NY 10036, USA has the following dimensions: Carpet Area of 50 चौ.मी. or 550 sqft, Balcony Area of 10 चौ.मी. or 110 sqft, Terrace Area of 20 चौ.मी. or 220 sqft.

<tbody>
</tbody>

If, in "चौ.मी.", those "dots" are both same as "decimal points", then try

=StrSum(A2;"चौमी")

Bye

 

[rahulajmera]

If, in "चौ.मी.", those "dots" are both same as "decimal points", then try

=StrSum(A2;"चौमी")

Bye

Thank you very much Anthony & Peter.
I have manually found & replaced "चौ.मी." with sqm and it should now do fine.

I am facing a much worse problem now.
My excel is crashing whenever I open the VBA editor.
Any idea how to solve that?

 

[Peter_SSs]

I am facing a much worse problem now.
My excel is crashing whenever I open the VBA editor.
Any idea how to solve that?

Some options to investigate
https://www.mrexcel.com/forum/excel-questions/746714-opening-visual-basic-editor-crashes-excel.html
https://www.excelforum.com/excel-general/1210273-workbook-crashes-when-attempting-to-open-vba-editor.html
https://techcommunity.microsoft.com/t5/Excel/Excel-crashes-on-attempting-to-open-VBA-editor/td-p/131524