# Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have text and number in multiple cells (Ex: Cell 1: 4S, Cell 2: 3.5A, Cell 3: 8O) and want to sum the number values only (Ex: 4 + 3.5 + 8). How can I do this?
Solved by O. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/04/2018 - 11:25
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert 04/04/2018 - 11:26
Do you want us to get the sum of the values?
User 04/04/2018 - 11:26
Yes
User 04/04/2018 - 11:27
I have been trying LEFT function, FIND function, SUMIF function and combinations of all of them via research online
Excelchat Expert 04/04/2018 - 11:27
Okay. Let me show you how to solve this.
User 04/04/2018 - 11:28
I will need to function to apply over rows G:NH
User 04/04/2018 - 11:28
columns*
Excelchat Expert 04/04/2018 - 11:29
You mean the function to be applied over columns rather than rows?
User 04/04/2018 - 11:29
Yes
User 04/04/2018 - 11:29
Oh, the other criteria is I would like the function to distinguish between the A, S and O
User 04/04/2018 - 11:30
For example, if there are 2 "4A" among multiple "3.5S" then I only want the 2 "4" to be added (=8)
Excelchat Expert 04/04/2018 - 11:31
Okay.
Excelchat Expert 04/04/2018 - 11:35
Kindly clarify the criteria which you have mentioned in the last step.
User 04/04/2018 - 11:37
If I have a range of 5 numbers (4A, 10A, 5S, 5O, 2A), how do I sum the numeric values for those cells which have "A" in them?
Excelchat Expert 04/04/2018 - 11:38
Okay.
User 04/04/2018 - 11:38
I imagine I would need a function that first identified which cells have the letter "A," and then sums the numeric value in those cells (4 + 10 + 2 = 16)
Excelchat Expert 04/04/2018 - 11:38
Allow me to customize a function to illustrate this please.
User 04/04/2018 - 11:39
Thank you! I've been trying to figure this out all day!
Excelchat Expert 04/04/2018 - 11:40
Sorry, is the data consistent?
Excelchat Expert 04/04/2018 - 11:40
As in, a digit is always followed by a letter?
User 04/04/2018 - 11:40
Yes
Excelchat Expert 04/04/2018 - 11:41
Okay, that make it easier.
Excelchat Expert 04/04/2018 - 11:42
We will use a number of functions so that we extract the digit values from the text in cells.
User 04/04/2018 - 11:42
sounds great
Excelchat Expert 04/04/2018 - 11:43
Excelchat Expert 04/04/2018 - 11:43
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
Excelchat Expert 04/04/2018 - 11:43
Where A1 is the cell address with the text we want to extract a value.
Excelchat Expert 04/04/2018 - 11:44
To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number.
User 04/04/2018 - 11:44
So, for example: If I want to extract "A" then "A" will be in cell A1?
Excelchat Expert 04/04/2018 - 11:44
Not really
Excelchat Expert 04/04/2018 - 11:46
The first function I have provided is to return the position of where the value is.
User 04/04/2018 - 11:46
got it
User 04/04/2018 - 11:47
Can you see the spreadsheet that I see right now?
Excelchat Expert 04/04/2018 - 11:48
Yeah
Excelchat Expert 04/04/2018 - 11:48
For our case:
Excelchat Expert 04/04/2018 - 11:49
After location of the position, then we can extract the digits.
Excelchat Expert 04/04/2018 - 11:50
This is achieved using the LEFT and RIGHT functions which you initially mentioned.
Excelchat Expert 04/04/2018 - 11:51
Are we together?
User 04/04/2018 - 11:51
Yes
User 04/04/2018 - 11:52
Sorry, I just wanted to enter a FIND function that I was experimenting with
User 04/04/2018 - 11:53
1. Establish position, 2. Extract digits using LEFT and RIGHT functions.
Excelchat Expert 04/04/2018 - 11:53
Exactly.
User 04/04/2018 - 11:54
Is the function I just put in cell A7 workable/close to the type of function we will need? I couldn't figure out how to write it without using a "."
User 04/04/2018 - 11:55
But, I am open to using the "." if that works well. We can make the structure of all cell values "#.Letter"
Excelchat Expert 04/04/2018 - 11:56
Yeah, we can manipulate in whichever way. But since you said all the values are consistent, we will not need to do that.
User 04/04/2018 - 11:57
ok cool
User 04/04/2018 - 11:58
So first, how do I use the LEFT / FIND function without using the "." ?
Excelchat Expert 04/04/2018 - 11:58
You notice that not all values have a dot, some do not have.
Excelchat Expert 05/04/2018 - 12:01
Are you there?
User 05/04/2018 - 12:02
Yes
Excelchat Expert 05/04/2018 - 12:02
The case I gave you is for a general case, where a digit can be in an position in a text for example:
Excelchat Expert 05/04/2018 - 12:02
Apple30
Excelchat Expert 05/04/2018 - 12:03
In that case we need to extract 30.
Excelchat Expert 05/04/2018 - 12:03
But since your data is consistent, we can use the LEFT and LEN functions to solve it.
Excelchat Expert 05/04/2018 - 12:04
Let me illustrate on the sheet on the right.
User 05/04/2018 - 12:04
watching :D
Excelchat Expert 05/04/2018 - 12:05
I have used the function:
Excelchat Expert 05/04/2018 - 12:05
=LEFT(B3,LEN(B3)-1)
Excelchat Expert 05/04/2018 - 12:06
It extracts the last character which is a letter and leaves us with a digit.
Excelchat Expert 05/04/2018 - 12:06
Are we together up to that point?
User 05/04/2018 - 12:06
yes!
User 05/04/2018 - 12:07
this will be occurring inside a larger function, correct? We are just breaking down the part right now?
Excelchat Expert 05/04/2018 - 12:08
To sum up the values, we will use SUM function.
User 05/04/2018 - 12:10
But what if I only want the values that had A attached to them "summed"? i.e. the values "4" and "4" in columns B and E?
Excelchat Expert 05/04/2018 - 12:11
Okay.
Excelchat Expert 05/04/2018 - 12:12
That means we will introduce a condition and we will use SUMIF function.
Excelchat Expert 05/04/2018 - 12:13
We will use the RIGHT function to return the last value and then compare it with 'A'.
Excelchat Expert 05/04/2018 - 12:14
If it is true then we will sum it.
User 05/04/2018 - 12:14
ohh yes, that makes sense.
Excelchat Expert 05/04/2018 - 12:15
=SUMIF(RIGHT(B3)="A", range)
Excelchat Expert 05/04/2018 - 12:16
Where B3 is the cell with the text value.
User 05/04/2018 - 12:18
what do I put for the range?
Excelchat Expert 05/04/2018 - 12:19
Is the range of the values to be summed up.
Excelchat Expert 05/04/2018 - 12:19
I want you to notice that, for Excel to interpret the values converted as numbers we should use the VALUE function.
Excelchat Expert 05/04/2018 - 12:19
Notice this please in the sheet:
Excelchat Expert 05/04/2018 - 12:21
Does that make sense?
User 05/04/2018 - 12:22
Yes, I think this makes sense
Excelchat Expert 05/04/2018 - 12:23
Ensure when you sum them up use the condition:
Excelchat Expert 05/04/2018 - 12:23
RIGHT(B3)="A"
Excelchat Expert 05/04/2018 - 12:24
That will ensure that the values summed up are only the once with an 'A' letter.
Excelchat Expert 05/04/2018 - 12:24
I see time has run out.
Excelchat Expert 05/04/2018 - 12:24