I have a spreadsheet with a column that contains explanations for changes in
account values day to day. The cells contain numbers, text and abbreviations
for multiples of 1,000 and 1,000,000. For example, one cell might have the
following:

2.375mm wires, 600k p/i, 100.235mm mkt

Is it possible to create a macro that will remove the text, convert the
numbers to their actual values (2,375,000, 600,000 and 100,235,000) and sum
these? The descriptions might not necessarily be in the order they are above
but the abbreviations and descriptions should be consistant.

This should get you started.

It assumes the string ends with text as in your example.

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 = "." Then
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")
End Sub

--
Regards,
Tom Ogilvy


"RMO" wrote:

I have a spreadsheet with a column that contains explanations for changes in
account values day to day. The cells contain numbers, text and abbreviations
for multiples of 1,000 and 1,000,000. For example, one cell might have the
following:

2.375mm wires, 600k p/i, 100.235mm mkt

Is it possible to create a macro that will remove the text, convert the
numbers to their actual values (2,375,000, 600,000 and 100,235,000) and sum
these? The descriptions might not necessarily be in the order they are above
but the abbreviations and descriptions should be consistant.

Thanks so much. I forgot to mention one thing. Negative values would be
presented in parenthesis (i.e. (22k) wires, (1.23mm) mkt). The macro you
gave me doesn't seem to accomodate negative values.

Also, is it possible to write a function so the values can appear in a cell?

Thanks again.




"Tom Ogilvy" wrote:

This should get you started.

It assumes the string ends with text as in your example.

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 = "." Then
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")
End Sub

--
Regards,
Tom Ogilvy


"RMO" wrote:

I have a spreadsheet with a column that contains explanations for changes in
account values day to day. The cells contain numbers, text and abbreviations
for multiples of 1,000 and 1,000,000. For example, one cell might have the
following:

2.375mm wires, 600k p/i, 100.235mm mkt

Is it possible to create a macro that will remove the text, convert the
numbers to their actual values (2,375,000, 600,000 and 100,235,000) and sum
these? The descriptions might not necessarily be in the order they are above
but the abbreviations and descriptions should be consistant.