Excel Sum numbers in cells that also contain text

[Simont485]

I have a speadsheet that uses text and numbers in the same cell but I need to sum the numbers only.

I only want to sum the cells that contain the letter T or t. I can get T+8 next to T-6 to give the result a 2.

But I need the same result from D-t+8 next to E-t-2 or any combination of containing T or t

Many thanks in advance

 

[Becky]

Will the numbers always be single digits (ie less than 10)? If so, you can extract the number from each cell using this formula:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,2)),0)

Then you just need to sum the numbers extracted. I have used SEARCH instead of FIND because it is not case sensitive, and instead of specifying the letter you could link it to a cell instead if you'd prefer.

However this will not work if the number has more than one digit, so I'll need to rethink how to do it if that's the case.

 

[Simont485]

Will the numbers always be single digits (ie less than 10)? If so, you can extract the number from each cell using this formula:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,2)),0)

Then you just need to sum the numbers extracted. I have used SEARCH instead of FIND because it is not case sensitive, and instead of specifying the letter you could link it to a cell instead if you'd prefer.

However this will not work if the number has more than one digit, so I'll need to rethink how to do it if that's the case.

Thank you but yes the number could be 2 digits and also less than 1 as well.

For sum the number when T is the only text in a range, I have used:

=SUM(IF(LEFT(C4:E4)="T",--RIGHT(C4:E4,LEN(C4:E4)-1))) then ctrl/shift/enter

 

[Becky]

When T/t appears in a cell, is it always the last letter before the number? Or could there be something like T-e+3?

 

[Simont485]

When T/t appears in a cell, is it always the last letter before the number? Or could there be something like T-e+3?

yes it will always be the last letter before the number.

D-t-2 or E-t+2 of just T+2 or T-2

 

[Becky]

Ah ok, so my formula could be amended to this:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,LEN(A1)-SEARCH("t",A1))),0)

This methodology can be applied to your existing array formula as follows:

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0))

Just make sure you hit Ctrl + Shift + Enter otherwise it won't work as an array.

Hope this helps!

 

[Simont485]

Ah ok, so my formula could be amended to this:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,LEN(A1)-SEARCH("t",A1))),0)

This methodology can be applied to your existing array formula as follows:

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0))

Just make sure you hit Ctrl + Shift + Enter otherwise it won't work as an array.

Hope this helps!

That is amazing thank you!!

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0)) works perfectly!

Basically as long as a cell contains T-2 or T+2 or any number, any text to the left is not relevant....just what I wanted

 

[Becky]

You're welcome! I'm glad we got there in the end

 

[Simont485]

OK one problem solved another arises.

So I also have T-e which has its own separate value and is summed elsewhere. But T-e is not recognised by the SUM(IF(ISNUMBER formula and throws an #VALUE error!!

I now need it to ignore T followed by a Letter.

Is this possible?

 

[Becky]

Oh no, how annoying! Is that the only exception or are there others?

 

[Simont485]

Oh no, how annoying! Is that the only exception or are there others?

There are other a lot of others, about 15 possible letters that could follow it. I have a work around that just means not using T in those cases but it would be preferable not to.

I was using SUM(COUNTIF(range{"T-e", "T-m"......})*{7,8,.....}) for the other combinations I have

 

[Becky]

Well this isn't exactly elegant, but it's the only way I can think to fix it...

=SUM(IFERROR(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0),0))

Basically it will count any errors as 0.

Let me know if it works!

 

[Simont485]

Elegant or not it works!

Magnificent!

I wont get a load of emails complaining that I changed a T to an H

Thank you so much!

 

[Becky]

I'm glad to hear it! No trouble at all, you're very welcome

 

[Simont485]

I have used this to sum hours booked as toil "t"
=SUM(IFERROR(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0),0))

I have used the same equation for holiday "h". (replaced t with h) It works a treat!

But it doesn't work where a cell has H-4/T-2 this happens when someone takes holiday and toil to take a day off.

It is possible to get the numbers associated to H and T out?

 

[Becky]

Just so I understand, you are looking for H, but T might also appear? So in the above example the answer would be -6?

 

[Simont485]

Not quite. Id need to sum them separately.

Other cells would contain H-number or T-number only but not usually both. I sum all the H-number cells and T-number cells separately

It is just on occasion I have one cell that contains both H and T followed by a number

 

[Becky]

I love a good problem to solve

The reason it's not being picked up is that the formula is designed to pick up the number from the end of the cell that contains the relevant letter. So if we wanted to change it to pick up the number from the middle of the cell then we'll have to change how it works. We could use FIND to see at which point in the string the letter is, and then we could use MID to extract the substring (ie the number)... however, we won't know the length of that the number, which complicates things. You said before that the numbers can be 2 digits, is that right? Or is it the case that cells which feature T and H will only feature single digits?

 

[Simont485]

Basically T or H could appear anywhere in a cell...followed by a dash with any number afther them. SO one cell could contain D-e-H-2.5-T-4.5 or D-e-T-4.5-H-2.5 or pretty much any order there of.

I need to get the numbers for T and H

The numbers could be upto 3 digits such as 10.5 (or is that 4?)

I feel it would be easier to change the spreadsheet to allow for text and numbers to be separated but for now that cant happen

 

[Becky]

Oh crikey! So the numbers that appear in the middle of the cell would be bounded by the letter on one side and a dash on the other? Would it ever be a different sign?

I feel it would be easier to change the spreadsheet to allow for text and numbers to be separated but for now that cant happen

Yup, it would all be far easier if the data could be entered in different cells. Why is it that some people are so averse to change?! I bet once they got used to it they would find it easy!