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.

 

[Peter_SSs]

Welcome to the MrExcel board!

I would suggest a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function sqm(s As String) As Double
  Static RX As Object
  Dim M As Object
  Dim itm As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\d+(\.\d+)?(?= *sqm)"
  Set M = RX.Execute(s)
  For Each itm In M
    sqm = sqm + itm
  Next itm
End Function

Excel Workbook
	A	B
1	Data	Sum sqm
2	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	80
3	This one is 45.6 SQM with an extra 20.2sqm possible	65.8
Sheet1

 

[Anthony47]

The User defined Function published by Peter already do the job (I genuinely envy those who can use RegularExpressions so well).

Nevertheless let mu publish a different UDF, that allows also specifying the measuremet unit and manages decimal numbers.
The code:

Function StrSum(ByVal Descr As String, Optional ByVal Unit As String = "sqm") As Single
Dim myW, I As Long, lSum As Single
'
myW = Split(Descr, " ", , vbTextCompare)
For I = 0 To UBound(myW) - 1
    If Replace(Replace(myW(I + 1), ",", "", , , vbTextCompare), ".", "", , , vbTextCompare) = Unit Then
        If IsNumeric(myW(I)) Then lSum = lSum + myW(I)
    End If
Next I
StrSum = lSum
Debug.Print StrSum
End Function

Then you will use the formula

=StrSum(A2)

You can specify the measurement unit (ie wich term looks for in the string); for example:

=StrSum(A2,"sqft")

If the measurement unit is omitted, then "sqm" is assumed.

Bye

 

[rahulajmera]

Thank you very much sir!
This has answered my query perfectly. But it has not solved my problem.

The string data that I'm dealing with is in native language, which is difficult to translate accurately.
The preceding text before the numbers is "चौ.मी"
Now I cant paste that font on to the Visual Basic Window as it is not in English.

How can I proceed now?

 

[Anthony47]

Did you try my function, using the second notation? IE inserting into the formula your measurement unit? Something like

=StrSum(A2,"चौ.मी")

Bye

 

[rahulajmera]

Did you try my function, using the second notation? IE inserting into the formula your measurement unit? Something like

=StrSum(A2,"चौ.मी")

Bye

Yes sir, I did try that, but it didn't work.
Elegant solution, btw.

 

[Peter_SSs]

Nevertheless let mu publish a different UDF, that allows also specifying the measuremet unit and manages decimal numbers.

Note that my udf already handles decimal numbers - see row 3 in my previous post.

It also handles if
- the units are in upper or lower case (row 3 below)
- the space is omitted between the number and the units (row 3 again)
- there is other punctuation besides comma or period after the units (rows 4 & 5)

To allow for other units, the adaptation of my function would be

Function SumUnits(s As String, Optional units As String = "sqm") As Double
  Static RX As Object
  Dim M As Object
  Dim itm As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\d+(\.\d+)?(?= *" & units & ")"
  Set M = RX.Execute(s)
  For Each itm In M
    SumUnits = SumUnits + itm
  Next itm
End Function

A comparison of data and formula results is shown below. Problem results highlighted yellow.

Excel Workbook
	A	B	C	D	E
1	Data	Peter Default Units	Peter Stated units	Anthony Default Units	Anthony Stated units
2	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.	80	880	80	880
3	This one is 45.6 SQM with an extra 20.2sqm possible	65.8	0	0	0
4	Is this house bigger than 45 sqm?	45	0	0	0
5	This house is 450 sqft; or 45 sqm	45	450	45	0
Sheet2

 

[Peter_SSs]

The preceding text before the numbers is "चौ.मी"

Is that the units?
Before the numbers?

 

[Anthony47]

I am sorry, but I cannot test my code with your native language. I used "space" as words separator; probably it need to be changed in your language.
Bye

 

[rahulajmera]

Is that the units?
Before the numbers?

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>