Formula to sum all text values from specific strings

V

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
368
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello!

I have imported data from a large Word table. They include also values, stored as text in strings from specific cells (see A3, A7, A11 etc.). I need a formula to convert all text values from each string to numbers, and sum the resulted values in the next cell (B1, B4, B8 etc.).
Thank you!

Book1
AB
1Rețele Electrice Muntenia
2(Bucuresti, Giurgiu, Ilfov)
30.28329 0.28329 0.03303 0.01151 0.290.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.59178 0.3214544
4DEER - SDEE Muntenia Nord
5(Braila, Buzau, Dambovita, Galati,
6Prahova, Vrancea)
70.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.671 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.62587 0.591
8DEER - SDEE Transilvania Sud
9(Alba, Brasov, Covasna, Harghita,
10Mures, Sibiu)
110.28329 0.28329 0.033 0.01151 0.35 0.11483 0.67876 0.44266 0.0725416 0.62587 0.80000 0.62587 0.533 0.591444
12DEER - SDEE Transilvania Nord
13(Satu Mare, Maramures, Slaj,
14Bihor, Cluj, Bistrita Nasaud)
150.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.67876 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.62587
16Rețele Electrice Banat (Arad,
17Caras-Severin, Hunedoara, Timis)
180.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.59178
19Distributie Energie Oltenia
20(Arges, Dolj, Gorj, Mehedinti, Olt,
21Teleorman, Valcea)
220.28329 0.28329 0.03303 0.01151 0.36353 0.13614 0.69136 0.46397 0.0725416 0.00350 0.00724 0.92182 0.65123 0.80000 0.65123
23Rețele Electrice Dobrogea
24(Calarași, Constanta, Ialomita,
25Tulcea)
260.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.59178
27Delgaz Grid (Bacau, Botosani,
28Iasi, Neamt, Suceava, Vaslui)
290.28329 0.28329 0.03303 0.01151 0.35323 0.11348 0.68106 0.44131 0.0725416 0.00350 0.00724 0.90957 0.62426 0.80000 0.62426
Sheet1
 
Sort by date Sort by votes
Hello,
What about
Excel Formula: 
=REDUCE(0, TEXTSPLIT(A3, " "), LAMBDA(a, v, a+v))
or
Excel Formula: 
=SUM(VALUE(TEXTSPLIT(A3, " ")))
However there is a small mistake in the cell A3 where you have 2 numbers not separated by a space ("0.290.08618"), so it returns an error. Otherwise the formula should give you the expected result.
 
Upvote 0
some of the numbers have 2 decimal places
=SUM(TEXTSPLIT(A2," ",)*1)
copy doen you will get an error for non numbers
so
=iferror(SUM(TEXTSPLIT(A2," ",)*1),"")

Book1
AB
1Rețele Electrice Muntenia 
2(Bucuresti, Giurgiu, Ilfov) 
30.28329 0.28329 0.03303 0.01151 0.290.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.59178 0.3214544 
4DEER - SDEE Muntenia Nord 
5(Braila, Buzau, Dambovita, Galati, 
6Prahova, Vrancea) 
70.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.671 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.62587 0.5915.8233916
8DEER - SDEE Transilvania Sud 
9(Alba, Brasov, Covasna, Harghita, 
10Mures, Sibiu) 
110.28329 0.28329 0.033 0.01151 0.35 0.11483 0.67876 0.44266 0.0725416 0.62587 0.80000 0.62587 0.533 0.5914445.4460656
12DEER - SDEE Transilvania Nord 
13(Satu Mare, Maramures, Slaj, 
14Bihor, Cluj, Bistrita Nasaud) 
150.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.67876 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.625875.2401516
16Rețele Electrice Banat (Arad, 
17Caras-Severin, Hunedoara, Timis) 
180.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.591784.9333216
19Distributie Energie Oltenia 
20(Arges, Dolj, Gorj, Mehedinti, Olt, 
21Teleorman, Valcea) 
220.28329 0.28329 0.03303 0.01151 0.36353 0.13614 0.69136 0.46397 0.0725416 0.00350 0.00724 0.92182 0.65123 0.80000 0.651235.3736816
23Rețele Electrice Dobrogea 
24(Calarași, Constanta, Ialomita, 
25Tulcea) 
260.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.591784.9333216
27Delgaz Grid (Bacau, Botosani, 
28Iasi, Neamt, Suceava, Vaslui) 
290.28329 0.28329 0.03303 0.01151 0.35323 0.11348 0.68106 0.44131 0.0725416 0.00350 0.00724 0.90957 0.62426 0.80000 0.624265.2415716
30 
Sheet1
Cell Formulas
RangeFormula
B1:B30B1=IFERROR(SUM(TEXTSPLIT(A1," ",)*1),"")
 
Upvote 0
Solution
etaf said:
some of the numbers have 2 decimal places
=SUM(TEXTSPLIT(A2," ",)*1)
copy doen you will get an error for non numbers
so
=iferror(SUM(TEXTSPLIT(A2," ",)*1),"")

Book1
AB
1Rețele Electrice Muntenia 
2(Bucuresti, Giurgiu, Ilfov) 
30.28329 0.28329 0.03303 0.01151 0.290.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.59178 0.3214544 
4DEER - SDEE Muntenia Nord 
5(Braila, Buzau, Dambovita, Galati, 
6Prahova, Vrancea) 
70.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.671 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.62587 0.5915.8233916
8DEER - SDEE Transilvania Sud 
9(Alba, Brasov, Covasna, Harghita, 
10Mures, Sibiu) 
110.28329 0.28329 0.033 0.01151 0.35 0.11483 0.67876 0.44266 0.0725416 0.62587 0.80000 0.62587 0.533 0.5914445.4460656
12DEER - SDEE Transilvania Nord 
13(Satu Mare, Maramures, Slaj, 
14Bihor, Cluj, Bistrita Nasaud) 
150.28329 0.28329 0.03303 0.01151 0.35093 0.11483 0.67876 0.44266 0.0725416 0.00350 0.00724 0.90683 0.62587 0.80000 0.625875.2401516
16Rețele Electrice Banat (Arad, 
17Caras-Severin, Hunedoara, Timis) 
180.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.591784.9333216
19Distributie Energie Oltenia 
20(Arges, Dolj, Gorj, Mehedinti, Olt, 
21Teleorman, Valcea) 
220.28329 0.28329 0.03303 0.01151 0.36353 0.13614 0.69136 0.46397 0.0725416 0.00350 0.00724 0.92182 0.65123 0.80000 0.651235.3736816
23Rețele Electrice Dobrogea 
24(Calarași, Constanta, Ialomita, 
25Tulcea) 
260.28329 0.28329 0.03303 0.01151 0.29408 0.08618 0.62191 0.41401 0.0725416 0.00350 0.00724 0.83918 0.59178 0.80000 0.591784.9333216
27Delgaz Grid (Bacau, Botosani, 
28Iasi, Neamt, Suceava, Vaslui) 
290.28329 0.28329 0.03303 0.01151 0.35323 0.11348 0.68106 0.44131 0.0725416 0.00350 0.00724 0.90957 0.62426 0.80000 0.624265.2415716
30 
Sheet1
Cell Formulas
RangeFormula
B1:B30B1=IFERROR(SUM(TEXTSPLIT(A1," ",)*1),"")
Click to expand...
Thank you, etaf ! Your formula works very well!
 
Upvote 0
saboh12617 said:
Hello,
What about
Excel Formula: 
=REDUCE(0, TEXTSPLIT(A3, " "), LAMBDA(a, v, a+v))
or
Excel Formula: 
=SUM(VALUE(TEXTSPLIT(A3, " ")))
However there is a small mistake in the cell A3 where you have 2 numbers not separated by a space ("0.290.08618"), so it returns an error. Otherwise the formula should give you the expected result.
Click to expand...
Thank you saboh12617 ! I tested your solution and it is available, too.
 
Upvote 0
You must log in or register to reply here.

Share this page

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