Solved: TRIM STRING

[khalid79m]

:help Hi I need some help triming strirgs, the strings vary alot , pretty much inconsistent ..

for example..

a1 = words(1),found(6),None(3)
a2 = words(3),found(90),None(324),found(31),None(323)

I need to add the numbers together

a1 = 1 + 6 + 3
a2 = 3 + 90 + 324 + 31 + 323

can anyone help ..im lost .. I can do simple trim but this is way over my head

[stanleydgrom]

khalid79m,

Detach/open workbook "Sum numbers in strings - khalid79m - SDG12.xls", and run macro "SumStrings".

The macro will create a formula in column B respective row, for each string in column A respective row.

Have a great day,
Stan

[bonyclyd]

Hi

I guess the regular expression would be best way to solve your problem.
However if you don't use that, you can do it as following:

Sub TrimString()
Dim a2 As String
Dim a2Tmp As String
Dim a2Split
Dim a2Sum As Double
Dim char As String, oldchar As String
Dim i As Long

a2 = ".w.ords(0.3),found(90),None(3.24),found(31),None(323)"

oldchar = Space(1)
For i = 1 To Len(a2)
char = Mid(a2, i, 1)

If Asc(char) = 46 And Asc(oldchar) >= 48 And Asc(oldchar) <= 57 Then
a2Tmp = a2Tmp & char
ElseIf Asc(char) >= 48 And Asc(char) <= 57 Then
a2Tmp = a2Tmp & char
Else
a2Tmp = a2Tmp & ","
End If
oldchar = char
Next

a2Split = Split(a2Tmp, ",")
For i = LBound(a2Split) To UBound(a2Split)
If Len(a2Split(i)) <> 0 Then a2Sum = a2Sum + CDbl(a2Split(i))
Next

Debug.Print a2Sum
End Sub


I also consider the string in case of having a float-pointing number.
HTH

[tpoynton]

As I slugged through this, Stan posted. A different approach...


Public Function GetNums(myString As String)
Dim i As Long
Dim mySum As Long
For i = 1 To Len(myString)
If Mid(myString, i, 1) = "(" Then
mySum = mySum + Mid(myString, i + 1, WorksheetFunction.Search(")", myString, i + 2) - (i + 1))
End If
Next i
GetNums = mySum
End Function

[mbarron]

Another option....

Function Addem(myCell) As Long
Dim arrStr() As String, i As Integer
arrStr = Split(myCell, "(")
For i = 1 To UBound(arrStr)
Addem = Addem + Val(arrStr(i))
Next
End Function

[khalid79m]

Hi Stan , your attachment works great I just need to make a few adjustments in that the situation has changed since i orignally posted, all numbers are inside brackets

eg (45) will your code support this ?? Im not sure on how to change it

[stanleydgrom]

khalid79m,


a1 = words(1),found(6),None(3)
a2 = words(3),found(90),None(324),found(31),None(323)


If cell A1 contains:
words(1),found(6),None(3)

Cell B1 will contain (per your original request) the formula:
=1+6+3
And, will display:
10


If cell A2 contains:
words(3),found(90),None(324),found(31),None(323)
Cell B2 will contain (per your original request) the formula:
=3+90+324+31+323
And, will display:
771


If the above is not correct, then please attach your workbook (or a sample containing no sensative information) - scroll down and see "Manage Attachments".

[khalid79m]

work perfectly thanks