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
We will start with this:
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
Hope that was helpful.
