Excel - Summing numbers

y2viks

y2viks

y2viks

Associate
Joined
31 Dec 2003
Posts
1,652
Location
I Freed Fishfingers 13/08
Hi,

In Excel i need to add numbers in a row together but the problem i have it that in the cells are occasionally words.

for example:

|viks 10|steve 6|

is there a way i could get excel to look at that but answer 16? At the moment i am using Sum and it comes up with 0.

Thanks

Viks
 
I think you will need to use VBA to do something like that as I do not think Excel can do it straight off. I will have a little think for you. :)

TrUz
 
Your right TrUz.

You would need to write a function with two arguments, one the start cell and one the end cell (to add up).

The function would then need to cycle through each cell and parse each string for the numbers.

There are usualy built in methods to split strings around white space and put them into arrays. You could then examine each element in the array to see if it was letters or numbers. Of course you would need to convert the string to a different type before doing any operations on it.

However, this is a complex problem: what if you had '12 abc 34'...what does it do then?

Justin
 
The information given so far would seem to be good info :)

But I hope you won't mind if I chip in and say:

This problem is only hard because it would appear that you are trying to use Excel "incorrectly"

I could be wrong - and there may be a very specific reason why your data is entered in "mixed" format (numbers and letters in the same cell)

But if not - then you really should re-think the layout of your data - as Excel is a very powerful program. But it is a SPREADSHEET that you are working with - so you should really consider whether you could have entered your data in a better way initially.....

I hope you don't take this the wrong way - it was just an observation, that may help in the future.....
 
daven1986 said:
if you used 2 cells for each item of data it would simplify your problem 100 times!

daven
Click to expand...

Copy the column with the mixed data and paste it into notepad and save as a txt.

Now use Data->Get External Data->Import Text file

Now use the white space to delimit the values into two columns.

I know just enough VBA to write some code to copy the numeric values out the cells but tbh the above method should get you close.

;)
 
You can actually do the above in Excel...

Data > Text to Columns
- Delimited > Next
- Space = True, Treat consecutive delimiters as one = True > Finish

Then Sum the newly created column.

This will split your mixed column into two columns, using the space as the separating factor.
 
mark66 said:
You can actually do the above in Excel...

Data > Text to Columns
- Delimited > Next
- Space = True, Treat consecutive delimiters as one = True > Finish

Then Sum the newly created column.

This will split your mixed column into two columns, using the space as the separating factor.
Click to expand...

Well spotted! :D
 
You must log in or register to reply here.
Back
Top Bottom