Help On Excel Required.

pierre92

Hi,
Im not a total begginer with excel i know quite a few things and how to do them. But one thing im puzzled about, is that in a cell (J7) i have two numbers, seperated by a comma and then a space.
Is there a forumala which is able to seperate these, and if not guessing that it will have to be a macro how can i go about it?
Thanks

flatmouse

Select cell
Choose Tools, Text to Column
change delimiter to be ,
done

flatmouse

Sorry under Data not under Tools
(keep delimited selected then for delimiter if you select , and space you don't get space before number)
then finish

trcooke

First number before the comma-space

=LEFT(A1;FIND(", "; A1)-1)

Second number after the comma-space

=MID(A1;FIND(", ";A1)+1;LEN(A1))

pierre92

Thanks thats sorted! :-)
Now another quick little problem.
I want a macro that calculates that if a cell is equal to 1, then it should add one to cell A1.
How would this be done?
Thanks

trcooke

So what you really want to do is count how many times '1' appears in a range of cells right? If so then you don't need a macro, just the following formula in cell A1:

=COUNTIF(B1:B12;1)

replace B1:B12 with your range of cells.

pierre92

Not really...but ive scraped that anyway now!
But is there a way to automatically run a macro when the sum of range of cells is equal to zero?
Thanks

trcooke

What do you want your macro to do? If you just want the cell to change colour or something simple like that then all you'd need to do is set up some conditional formatting for that cell.

If you really do want to run a macro then be prepared for a steep learning curve as they're not as easy as the basic formula's you've been asking about so far.

PhilCo_2

If you right click on the sheet tab and go to View Code and put this in...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim sumrange As Range
Set sumrange = Sheets("sheet1").Range("B4:B6")
If Application.WorksheetFunction.Sum(sumrange) = 0 Then
MsgBox "Range = 0"
Else
End If

End Sub

it will run everytime the sheet is changed and bring up a messagebox when the value of B4:B6 on sheet 1 equals 0. You'll just need to change the sheet and range to match and change the messagebox bit to whatever calculation you want.