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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=388562
>

[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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"mikeburg" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=388562
>

[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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"mikeburg" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=388562
>