Function to Sum A Cell Containing Numbers and Text Abbriviations

[RMO]

I have a spreadsheet containing a cell with comments in the following format
to explain changes in value:

1mm mkt, (3.23mm) jnl, 115k px

The comments might change but "mm" will always represent 1,000,000, "k" will
always represent 1,000 and negative values will be in parenthesis.

Is it possible to write a function that removes the comments, converts the
values to their real values and sums them? In the example above the
function should produce a value of negative 2,115,000.

Thank you.

 

[Tom Ogilvy]

Why not start with the last function you were given and modify that?

 

[Tom Ogilvy]

Sub Sumstrings()
Dim tot As Double, s As String
Dim i As Long, Mult As Double
s1 = ActiveCell.Text
For i = 1 To Len(s1)
sChr = Mid(s1, i, 1)
If IsNumeric(sChr) Or sChr = "." Or sChr = "(" Then
If sChr = "(" Then
sChr = "-"
End If
s = s & sChr
Else
If Len(s) > 0 Then
Select Case sChr
Case "m"
Mult = 1000000
Case "k"
Mult = 1000
Case Else
Mult = 1
End Select
tot = tot + CDbl(s) * Mult
s = ""
End If
End If
Next
'MsgBox Format(tot, "#,##0")
activecell.Offset(0,1).Value = Format(tot, "#,##0")
Activecell.Offset(0,1).Numberformat = "#,##0;(#,##0)"
End Sub