text and numbers in same cell, sum of numbers ?

[skgurmeet]

Dear All,

i have query, please help out.

multiple numbers and text are in single cell which are separated through spaces or comma, is there any formula which can bypass text and add all the numbers in the cell.

For example:

<table style="width: 384px; height: 83px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 125pt;" width="167"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 125pt;" height="20" width="167">red 12, yellow 10, green 5</td> <td class="xl63" style="border-left: medium none; width: 48pt;" align="right" width="64">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">red 3, blue 9</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">yellow 21, blue 3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">24</td> </tr> </tbody></table>
Thanks,
Gurmeet

 

[alireds]

yes like picture
some cells include "h[number]" and some cells include "h[number]-"

 

[konew1]

This should do it

Excel Workbook
	I
1	c45cd
2	h70
3	c45cd
4	h65
5	h66-
6	h670
7
8	871
Sheet8

 

[alireds]

I can't say anything, you are a genius
Thank you a lot

 

[konew1]

I can't say anything, ...
Thank you a lot

You have, thanks for the feedback

 

[Danadams2504]

Hi all,

I've been following this thread hoping it would help me with an issue I'm having. I'm working on a planner for our operatives on shift and I want to count their hours for costs calculations.

For each day they'll have a single letter code and number to dictate what shift and for how long they worked. For example: N12 - nightshift 12 hours.

I understand the value function and can extract the number from a single cell: =SUM(VALUE(RIGHT(HB8,2))) - gives me the 12 from N12. What I can't seem to get working is the function to sum multiples of these value functions without the largest formula ever: =SUM(VALUE(RIGHT(HB8,2)),(VALUE(RIGHT(HC8,2))....

An example from my spreadsheet would be like this:

Joe Blogs N12 N12 N15 N12 N12 D12 D12

I want to calculate the total hours on nightshift for him and on dayshift (D) for him, separately.

Apologies if I've rambled, just bashing my head on a wall at the moment!

Dan

 

[István Hirsch]

Give this a try for night shift (confirm with Ctrl – Shift – Enter):


=SUM(IF(MID(A1,ROW($1:$255),2)=" N",IFERROR(--MID(A1,ROW($1:$255)+2,2),0),0))


For day shift change „ N” to „ D” in the formula.

 

[Danadams2504]

Hi Istvan,

I've tried the formula you supplied, putting it at the end of a row containing N12 etc values. After confirming with Ctrl-Shift-Enter, it gives a 0 as the result regardless of the preceding values.

=SUM(IF(MID(A1,ROW($1:$255),2)="N",IFERROR(--MID(A1,ROW($1:$255)+2,2),0),0))

 

[FormR]

An example from my spreadsheet would be like this:

Joe Blogs N12 N12 N15 N12 N12 D12 D12

I want to calculate the total hours on nightshift for him and on dayshift (D) for him, separately.

Hi, something like this maybe:

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1									N	D
2	Joe Blogs	N12	N12	N15	N12	N12	D12	D12	63	24
Sheet1

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(--("0"&MID(B2:H2,2,99)),--(LEFT(B2:H2,1)="N"))
J2	=SUMPRODUCT(--("0"&MID(B2:H2,2,99)),--(LEFT(B2:H2,1)="D"))

 

[István Hirsch]

Hi Istvan,

I've tried the formula you supplied, putting it at the end of a row containing N12 etc values. After confirming with Ctrl-Shift-Enter, it gives a 0 as the result regardless of the preceding values.

=SUM(IF(MID(A1,ROW($1:$255),2)="N",IFERROR(--MID(A1,ROW($1:$255)+2,2),0),0))

My formula works and gives the result below with this layout:

Excel Workbook
	A	B	C
1	Joe Blogs N12 N12 N15 N12 N12 D12 D12	63	24
2	Johnny Walker D5 N7 D2 D7 N5 D5	12	19
Sheet

I suggest that you copy the formula in its place, do not enter character by character (for example, my formula contains „ N” (space N) but you entered „N” (no space) - that can be the cause of the difference. Confirm with Ctrl – Shift – Enter not just Enter.

 

[Danadams2504]

...Your a wizard! For my own understanding do I have the following right:

=SUMPRODUCT: means its adding all values found
(--("0"&MID(B2:H2,2,99)): is looking for any string starting at the second position within a cell and taking up to 99 positions afterwards within the range of cells from b2 to h2. I don't understand the "0"& part or the --
--(LEFT(B2:H2,1)="D")): is checking if the cells have a "D" in position 1 (starting on the left) within the range of cells b2 to h2. Again I don't understand the --.

I'm also unsure how the formula prioritises checking for a D first before then getting the values afterwards. I appreciate your help and thank you again for the formula!

Kind regards,

Dan