Clever formula required to sum contents of cell

G

Gerry

I have a single column spread sheet. All cells contain text. However the text lists out separate amounts of money. An example is this (all of it being in a single cell - cell A88 in my case )

£5,175, £105,134, £2,175, £9,831, £10,681, £2,064, £50,853, £3,400, £6,422, £110,552, £433, £33,320, £76,055, £1,104, £24,051, £70,000, £1,966, £5,151, £1,687, £9,011

Another example is this (cell A47 in my case. I show this as it demonstrates negative amounts can be within the text string.

£65,587, £-25,005


Can someone provide me with a clever forumula which can put the sum of the amounts for Cell A1 into cell B1 such that I can copydown cell B1 to the end of my list (which goes down to cell A185)

Many thanks

Gerry.
 
C

Claus Busch

Hi Gerry,

Am Thu, 25 Apr 2013 04:16:06 -0700 (PDT) schrieb Gerry:
I have a single column spread sheet. All cells contain text. However the text lists out separate amounts of money. An example is this (all of it being in a single cell - cell A88 in my case )

£5,175, £105,134, £2,175, £9,831, £10,681, £2,064, £50,853, £3,400, £6,422, £110,552, £433, £33,320, £76,055, £1,104, £24,051, £70,000, £1,966, £5,151, £1,687, £9,011

Another example is this (cell A47 in my case. I show this as it demonstrates negative amounts can be within the text string.

£65,587, £-25,005
Click to expand...

put the following function in a standard modul and call the function in
the sheet e.g. with:
=mySum(A88)

Function mySum(myRange As Range) As Double
Dim myArr As Variant
Dim myStr As String
Dim i As Integer

myStr = Replace(Replace(myRange, ", £", ";"), "£", "")
myArr = Split(myStr, ";")
For i = LBound(myArr) To UBound(myArr)
mySum = mySum + myArr(i)
Next
End Function


Regards
Claus Busch
 
G

Gerry

Claus, thanks very much. I have never written a module or created a function. Can you give me a pointer as to how to do that, and then how to call it up into all the cells in column B?
Thanks in advance
Gerry
 
G

Gerry

I have a single column spread sheet. All cells contain text. However the text lists out separate amounts of money. An example is this (all of it being in a single cell - cell A88 in my case )



£5,175, £105,134, £2,175, £9,831, £10,681, £2,064, £50,853,£3,400, £6,422, £110,552, £433, £33,320, £76,055, £1,104, £24,051, £70,000, £1,966, £5,151, £1,687, £9,011



Another example is this (cell A47 in my case. I show this as it demonstrates negative amounts can be within the text string.



£65,587, £-25,005





Can someone provide me with a clever forumula which can put the sum of the amounts for Cell A1 into cell B1 such that I can copydown cell B1 to the end of my list (which goes down to cell A185)



Many thanks



Gerry.
Click to expand...
 
G

Gerry

Claus, thanks very much. I have never written a module or created a function. Can you give me a pointer as to how to do that, and then how to call it up into all the cells in column B?

Thanks in advance

Gerry
Click to expand...
 
C

Claus Busch

Hi Gerry,

Am Thu, 25 Apr 2013 05:14:55 -0700 (PDT) schrieb Gerry:
I have never written a module or created a function. Can you give me a pointer as to how to do that, and then how to call it up into all the cells in column B?
Click to expand...

press Alt + F11 => Insert => Standard module and paste the code in this
module.
In the sheet you write in B1:
=mySum(A1) and copy down.
if you don't get it to work, have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Gerry"
You have to right-click and download because macros are disabled in
SkyDrive.


Regards
Claus Busch
 
G

Gerry

Hi Gerry,



Am Thu, 25 Apr 2013 05:14:55 -0700 (PDT) schrieb Gerry:






press Alt + F11 => Insert => Standard module and paste the code in this

module.

In the sheet you write in B1:

=mySum(A1) and copy down.

if you don't get it to work, have a look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191

for the workbook "Gerry"

You have to right-click and download because macros are disabled in

SkyDrive.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

THanks so much. THat worked. Gerry
 
Top