Sum up concatenation values

[default_name]

Hello,

Is there any way to sum up the numeric values of concatenations?

Example below.
The data displayed in cells A2:D2 are a result of some concatenations.
In cell E2 I would like to be able to sum up those concatenated values.
In this case, E2 should come up as 137 days.

Stipulations:
I cannot remove those concatenations in columns A:D.
I cannot use macros/VBA for this.

Is these any way this can be done? Thanks

	A	B	C	D	E
1	Step 1	Step 2	Step 3	Step 4	Total Days
2	5 days	28 days	103 days	1 day
3

 

[gaz_chops]

Try

=SUMPRODUCT((LEFT(A2:D2,FIND(" ",A2:D2)))*1)

 

[default_name]

Try

=SUMPRODUCT((LEFT(A2:D2,FIND(" ",A2:D2)))*1)

View attachment 15713

Thanks for the quick response.
I tried entering in that formula but it resulted in a #VALUE!

Perhaps it may help if I describe the formulas being used in the concatenated cells.
This is what is in cell A2.

=CONCATENATE(COUNTIF(A5:A100,"*y*"),CHAR(10),"days")

 

[lrobbo314]

Here is a way using Power Query.

Book1
	A	B	C	D	E
1	Step 1	Step 2	Step 3	Step 4	Total
2	5 days	28 days	103 days	1 day	137
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Total = Table.AddColumn(Source, "Total", each List.Sum(List.Transform(Record.FieldValues(_), each Int64.From(Text.Split(_," "){0}))))
in
    Total

 

[gaz_chops]

Hmm appears to be the fact that you are using CHAR(10)!

 

[gaz_chops]

Try

=SUMPRODUCT(--(LEFT(A4:D4,FIND("d",A4:D4)-2)))

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1	Step 1	Step 2	Step 3	Step 4	Total Days
2	5 days	28 days	103 days	1 day	137
Data

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(LEFT(A2:D2,FIND(CHAR(10),A2:D2)-1)))

 

[default_name]

Try

=SUMPRODUCT(--(LEFT(A4:D4,FIND("d",A4:D4)-2)))

How about

+Fluff New.xlsm
	A	B	C	D	E
1	Step 1	Step 2	Step 3	Step 4	Total Days
2	5 days	28 days	103 days	1 day	137
Data

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(LEFT(A2:D2,FIND(CHAR(10),A2:D2)-1)))

I tried both of these formulas and they both result in a #VALUE!

Columns B:D contain options for both days and day (in the case of "1 day") like this:
Here is what is in B2.

=IF((COUNTIF(B5:B100,"*y*")=1),CONCATENATE(COUNTIF(B5:B100,"*y*"),CHAR(10),"day"),CONCATENATE(COUNTIF(B5:B100,"*y*"),CHAR(10),"days"))

Could this be causing the issue? For day vs days consistency I can also make A2 look like B2:D2.

 

[default_name]

SOLVED IT!
My actual spreadsheet had some columns with merged values.
I unmerged the columns and it all works perfectly.

Thanks Fluff and gaz_chops!
Also thank you to lrobbo314 for providing a vba option if it ever needed it.

You guys are awesome!

 

[Fluff]

Glad you sorted it & thanks for the feedback.
Best advice is NEVER use merged cells.