Add only first numbers in each cell

ewso · #1 05-21-2017, 11:43 PM

I have a column with two numbers in each cell like this...

A1: 64 84
A2: 1000 87
A3: 14 999
A4: 32 65

I want to be able to add up only the first numbers in every cell, like this...

64
1000
14
32

...I don't want the second numbers in the cell to be included in the calculation. Is there a way I can do this?

Thanks

gmayor · #2 05-22-2017, 12:48 AM

It is easy enough with a macro. The following assumes the result will go in the last cell in the column containing the cursor and that there are no merged/split cells in the table.

Code:

Sub CountNums()
Dim oTable As Table
Dim lngCount As Long: lngCount = 0
Dim oCell As Cell
Dim oRng As Range
Dim i As Integer
Dim iCol As Integer
Dim iRow As Integer

Set oTable = Selection.Tables(1)
    iRow = oTable.Rows.Count
    If Selection.InRange(oTable.Range) = True Then
        iCol = Selection.Cells(1).ColumnIndex
        For i = 1 To iRow - 1
            lngCount = lngCount + Split(oTable.Columns(iCol).Cells(i).Range.Text, Chr(32))(0)
        Next i
        Set oRng = oTable.Columns(iCol).Cells(iRow).Range
        oRng.End = oRng.End - 1
        oRng.Text = lngCount
    End If
lbl_Exit:
    Set oCell = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

ewso · #3 05-22-2017, 01:08 AM

Quote:

Originally Posted by gmayor

It is easy enough with a macro. The following assumes the result will go in the last cell in the column containing the cursor and that there are no merged/split cells in the table.

Code:

Sub CountNums()
Dim oTable As Table
Dim lngCount As Long: lngCount = 0
Dim oCell As Cell
Dim oRng As Range
Dim i As Integer
Dim iCol As Integer
Dim iRow As Integer

Set oTable = Selection.Tables(1)
    iRow = oTable.Rows.Count
    If Selection.InRange(oTable.Range) = True Then
        iCol = Selection.Cells(1).ColumnIndex
        For i = 1 To iRow - 1
            lngCount = lngCount + Split(oTable.Columns(iCol).Cells(i).Range.Text, Chr(32))(0)
        Next i
        Set oRng = oTable.Columns(iCol).Cells(iRow).Range
        oRng.End = oRng.End - 1
        oRng.Text = lngCount
    End If
lbl_Exit:
    Set oCell = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

Hi, thanks for the code, but I get an error when I run it. It says...

Compile error:
User-defined type not defined

What could be wrong?

Guessed · #4 05-22-2017, 02:41 AM

You've asked this question in a Word forum but looking at the wording of your question, perhaps you are using Excel. Can you confirm that your data and macro is in Word?

ewso · #5 05-22-2017, 02:57 AM

Quote:

Originally Posted by Guessed

You've asked this question in a Word forum but looking at the wording of your question, perhaps you are using Excel. Can you confirm that your data and macro is in Word?

Sorry about that. I'm using Excel, not Word. Thanks for telling me. Can someone please move this question to the right forum?

Guessed · #6 05-22-2017, 03:12 AM

I'm not a moderator here so I can't move it but I can answer it.

Rather than adapting Graham's code to work in Excel, I would first suggest you do it without a macro. It is easy enough to separate the two numbers into two columns and then do a normal sum formula on the first column.

Select Column A and go to Data > Text to Columns and choose Delimited > Next > tick Space > Finish

ewso · #7 05-22-2017, 03:19 AM

Quote:

Originally Posted by Guessed

I'm not a moderator here so I can't move it but I can answer it.

Rather than adapting Graham's code to work in Excel, I would first suggest you do it without a macro. It is easy enough to separate the two numbers into two columns and then do a normal sum formula on the first column.

Select Column A and go to Data > Text to Columns and choose Delimited > Next > tick Space > Finish

Thanks guessed, that's a solution, but I actually need to keep them in one column because of other things going on in the spreadsheet, so hopefully I can get a macro that works in Excel.

Guessed · #8 05-22-2017, 03:45 AM

In that case, try this macro after selecting the cells you want totalled

Code:

Sub CountNums()
  Dim lngCount As Long: lngCount = 0
  Dim oRng As Range
  For Each oRng In Selection.Areas(1)
    lngCount = lngCount + Split(oRng.Value, " ")(0)
  Next oRng
  MsgBox "The first numbers add up to: " & lngCount
End Sub

xor · #9 05-22-2017, 04:15 AM

If you want a formula, try

=SUM(--(MID(A1:A4;1;FIND(" ";A1:A4)-1)))

which must be committed by holding down Ctrl and Shift before pressing Enter (array formula)

ewso · #10 05-24-2017, 09:43 AM

Thanks guys, that works!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sorting numbers within cell	michaelst	Excel	9	04-22-2017 10:51 PM
Difference between 2 numbers in same cell	Neila1982	Excel	1	02-12-2017 01:40 AM
Add Numbers from One or Another Cell	Kilch	Excel	3	01-31-2017 12:55 PM
Search a cell that contains words and numbers and convert the numbers to metric	Carchee	Excel Programming	36	10-08-2014 03:16 PM
adding numbers in same cell	jetdude68	Word Tables	1	07-20-2010 12:30 AM