Formula to strip figures from cells text strings

[mikeburg]

I'm trying to write a cell formula that strips figures from cells text
strings & totals them into one cell. The figures will always have a
decimal.

For example:

Cell A5 has a text string "924 Social 9.78"
Cell B5 has a text string "984 163.94 981 7.84"
Cell C5 has a text string "920 Kitchen 7.86
Cell D5 has a text string :988 Youth 42.46

Need to arrive a the total amount of 231.88 in cell E5
(9.78+163.94+7.84+7.86+42.46)-do not include the non-decimal figures
924, 984, etc.

Any ideas?

Thanks for all your help.

mikeburg

 

[Biff]

Hi!

This is going to be very difficult!

Are the quotes part of the string ?

"924 Social 9.78"

I see cell B5 has 2 decimal values. How many might there be ?

Biff

 

[mikeburg]

Biff:

No, the quotes are not part of the cell's contents.

Yes, the cell can contain one or two dollar amounts to be pulled &
totaled for that cell.

Any help you can give will be greatly appreciated!

mikeburg

 

[David McRitchie]

Hi Mike,

Assuming you can have commas which will be ignored and that
the dollar amounts must have exactly two decimals and that you
have no negative numbers.

Function getdollars(cell As String) As Double
Dim Str As String, i As Long, dswt As Integer
For i = 1 To Len(cell)
Select Case Mid(cell, i, 1)
Case "."
dswt = 1
Str = Str & Mid(cell, i, 1)
Case ","
Case "0" To "9"
Str = Str & Mid(cell, i, 1)
If dswt Then dswt = dswt + 1
Case Else
If dswt = 3 Then getdollars = getdollars + Str
Str = ""
dswt = 0
End Select
Next i
If dswt = 3 Then getdollars = getdollars + Str
End Function

B5: '984 163.94 981 7.84
C5: =getdollars(b5)

If not familiar with User Defined Functions you will find instructions
to install UDF and macros in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[mikeburg]

David,

The UDF works great! However, can you modify it to pull negative
numbers & use them in summing the total as a negative figure too?

Thanks so much. What we have here solves the problem most of the
time.

mikeburg

 

[David McRitchie]

Assuming that the negative sign immediately precedes the
number you would not add to the count but would include the
negative sign.


Case "-"
if Str = "" then Str = Mid(cell, i, 1)