Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mikeburg
 
Posts: n/a
Default Formula to strip figures from cells text strings


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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #3   Report Post  
mikeburg
 
Posts: n/a
Default


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

  #4   Report Post  
David McRitchie
 
Posts: n/a
Default

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" wrote in message
...

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



  #5   Report Post  
mikeburg
 
Posts: n/a
Default


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



  #6   Report Post  
David McRitchie
 
Posts: n/a
Default

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" wrote in message
...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 10:07 PM
text wrap in merged cells SteveFerd Excel Discussion (Misc queries) 3 July 16th 05 12:46 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
formula is displaying as text rather than result. Jamie Excel Worksheet Functions 2 December 13th 04 06:33 PM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"