Sum up concatenation values

default_name

Board Regular
Joined
May 16, 2018
Messages
170
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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

ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day
3
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try

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

1591634076464.png
 
Upvote 0
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.
Rich (BB code):
=CONCATENATE(COUNTIF(A5:A100,"*y*"),CHAR(10),"days")
 
Upvote 0
Here is a way using Power Query.

Book1
ABCDE
1Step 1Step 2Step 3Step 4Total
25 days28 days103 days1 day137
Sheet3


Code:
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
 
Upvote 0
How about
+Fluff New.xlsm
ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day137
Data
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT(--(LEFT(A2:D2,FIND(CHAR(10),A2:D2)-1)))
 
Upvote 0
Try

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

How about
+Fluff New.xlsm
ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day137
Data
Cell Formulas
RangeFormula
E2E2=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.
Rich (BB code):
=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.
 
Upvote 0
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!
 
Upvote 0
Glad you sorted it & thanks for the feedback.
Best advice is NEVER use merged cells. ;)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top