Clever formula required to sum contents of cell

  • Thread starter Thread starter Gerry
  • Start date Start date
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.
 
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
 
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
 
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...
 
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...
 
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
 
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
 
Back
Top