Excel Sum numbers in cells that also contain text

[Simont485]

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?



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!

That particular dash would always be there.

But there could be a + or - but only where the T appears. so it could be D-e-H-2.5-T+2.5

I have just been asked to try at make the spreadsheet do some of the maths. I am not allowed to change the 'look' of it. As you say, and from my experience, people do find it easy and better.........ho hum!!

 

[Becky]

Work would be so much easier without the other workers!

Here's what I've got so far:

=SUM(IFERROR(IF(ISNUMBER(SEARCH("t",C4:E4)),--MID(C4:E4,SEARCH("t",C4:E4)+1,4),0),0))

I'm using MID instead of RIGHT, and the syntax is as follows:

MID(text, start_num, num_chars)

So the text is easy, the start number is where the T (or H) occurs plus 1 (ie the character immediately after the searched letter), but the number of characters is where I'm struggling. As you can see I put in 4 which only works where there are 4 characters - eg +2.5

So ideally the number of characters would be the position of the first "-" after T/H less the position of T/H, unless the number is at the end of the string in which case the stopping point would be the end of the string.

I'm sure there must be a way to do it!

 

[Simont485]

Thanks for trying Becky but it is not an issue if it doesn't work.

Another variation that I am trying is for T.-6 so that the full stop is used for some conditional formatting. But when put into

SUM(IFERROR(IF(ISNUMBER(SEARCH("t.",$I7:$NI7)),--RIGHT($i7:$NI7,LEN($I7:$NI7)-SEARCH("t.",$I7:$NI7)),0),0))

as an array it shows zero. How do I get it to recognise t. ?

 

[Becky]

I think it's because there are two characters, so the RIGHT part of the formula needs to be modified slightly. Changing the "t." to "." should work

 

[Simont485]

I think it's because there are two characters, so the RIGHT part of the formula needs to be modified slightly. Changing the "t." to "." should work

Thank you again....I haven't tried it yet but there are numbers in the cells after the letters. So if there is t-6.5 wont it then see the decimal point as a full stop and sum too?

 

[Becky]

Hmmm good point... How about this instead:

RIGHT($I7:$NI7,LEN($I7:$NI7)-SEARCH("t.",$I7:$NI7)+1)

When you use the search function to find a term with multiple characters, it will return the position of the first character in that term.

So this new formula is looking at the total length of the string, minus the position of t, plus one. Obviously you'd only need to do this when looking for a letter followed by a decimal point, which means amending the formula. If this does what you want then we could rework it slightly to make it more intelligent (ie so you don't need to amend the formula).

 

[Simont485]

Ok thank you......

but, you knew there'd be one.......other letters are also used followed by a . The . is used as a trigger for some conditional formatting of cells containing it. I use D. and or D1. as well as other letters. These are not followed by numbers though.

Apologies and thanks in advance!!

 

[Becky]

How do you have things laid out in your spreadsheet? Do you use this formula for each different parameter, or do you have it linked to a cell in which you input what you're looking for? When you're looking for letters followed by a full stop, do you need it to search for all letters followed by a full stop or just certain ones?

 

[Simont485]

Basically the spreadsheet is a rota. i7:ni7 is a range for 1 person over 1 year.
In each cell a different shift appears. Depending on what happens to that shift or day determines what goes in that cell.

There is conditional formatting associated with different situation such as...overtime shows a coloured cell....that is triggered by a shift followed by a full stop

I have a count on another tab for holiday and toil. and for overtime per person. This is where I use this formula.

Is that any help?

 

[Becky]

That is helpful, but I was just trying to clarify how you want the formula changed - is it so that it picks up the number following any letter plus a full stop, or just specific letters?

 

[Simont485]

That is helpful, but I was just trying to clarify how you want the formula changed - is it so that it picks up the number following any letter plus a full stop, or just specific letters?

Ok so....I am looking to pick up a number that follows a specific letter followed by a full stop. T.6 or T.10.5

Other cells could contain T-6 or T-6.5 but the other formulas work for that situation.....

As it is a shift rota there are a lot of letters but only some are used that need to be added up....

 

[Becky]

Ok, so if the search term is fixed you could use this (as described above):

=SUM(IFERROR(IF(ISNUMBER(SEARCH("t.",$I7:$NI7)),--RIGHT($I7:$NI7,LEN($I7:$NI7)-SEARCH("t.",$I7:$NI7)+1),0),0))

Will it ever be longer than two characters (ie the letter and the full stop)?

 

[Simont485]

Ok, so if the search term is fixed you could use this (as described above):

=SUM(IFERROR(IF(ISNUMBER(SEARCH("t.",$I7:$NI7)),--RIGHT($I7:$NI7,LEN($I7:$NI7)-SEARCH("t.",$I7:$NI7)+1),0),0))

Will it ever be longer than two characters (ie the letter and the full stop)?

OK I will try it tomorrow when at work.

No it will only be T.

Thank you so much for your time.

 

[Becky]

No worries, hope it works!

 

[Simont485]

No worries, hope it works!

I have tried the formula but it returns the value zero. I have entered it as an array.

I have also been given this formula that seems to work.

=SUM(IF(LEFT(A1:F12)="T.",REPLACE(A1:F12,1,2,"")+0))

I admit I don't know how these formula work!

Thank you for you time