Excel Sum values by extracting numbers from single multi-line cellReturn empty cell from formula in ExcelPython: Extract numbers from a stringFind and extract a number from a stringExtract all cell references in excel with regex in javaUsing Excel to SUM values from multiple sheets using a date value as a lookupExcel | Searching table in different sheet and bringing value from column in that rowExcel conditional formating - conditional on sum of all previous cells and total sumHow to sum several bracket-surrounded numbers of a single cell with Excel formula?REGEXEXTRACT to extract values and sum up a series of numbersExtracting quantity (number) from long string of text
Why would the Red Woman birth a shadow if she worshipped the Lord of the Light?
How to prevent "they're falling in love" trope
What's the in-universe reasoning behind sorcerers needing material components?
What mechanic is there to disable a threat instead of killing it?
Why can't we play rap on piano?
Bullying boss launched a smear campaign and made me unemployable
When is человек used as the word man instead of человек
What is the most common color to indicate the input-field is disabled?
If human space travel is limited by the G force vulnerability, is there a way to counter G forces?
How could indestructible materials be used in power generation?
How dangerous is XSS?
Assassin's bullet with mercury
Alternative to sending password over mail?
How much of data wrangling is a data scientist's job?
Can my sorcerer use a spellbook only to collect spells and scribe scrolls, not cast?
Should I tell management that I intend to leave due to bad software development practices?
Can I run a new neutral wire to repair a broken circuit?
Avoiding the "not like other girls" trope?
Avoiding direct proof while writing proof by induction
One verb to replace 'be a member of' a club
What is the idiomatic way to say "clothing fits"?
What exploit Are these user agents trying to use?
How can saying a song's name be a copyright violation?
Why didn't Miles's spider sense work before?
Excel Sum values by extracting numbers from single multi-line cell
Return empty cell from formula in ExcelPython: Extract numbers from a stringFind and extract a number from a stringExtract all cell references in excel with regex in javaUsing Excel to SUM values from multiple sheets using a date value as a lookupExcel | Searching table in different sheet and bringing value from column in that rowExcel conditional formating - conditional on sum of all previous cells and total sumHow to sum several bracket-surrounded numbers of a single cell with Excel formula?REGEXEXTRACT to extract values and sum up a series of numbersExtracting quantity (number) from long string of text
AL-CHE-P1-1518 --- 270
AL-CHE-P2-1318 --- 280
AL-MAT-P1-1218 --- 280
AL-MAT-P4-0918 --- 40
all these data are inside same cell C2, my aim is to derive a formula to sum
270+280+280+40
in cell D2
tried regextract(c2,"d(.*)n") but only the first "270" is extracted, I need help, searched through all forums, couldn't get exact match, it will save me huge time if anyone could give me some hint on how to derive the sum inside same cell string
regex google-sheets excel-formula google-sheets-formula
edited Mar 8 at 0:25
player0
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
add a comment |
AL-CHE-P1-1518 --- 270
AL-CHE-P2-1318 --- 280
AL-MAT-P1-1218 --- 280
AL-MAT-P4-0918 --- 40
all these data are inside same cell C2, my aim is to derive a formula to sum
270+280+280+40
in cell D2
tried regextract(c2,"d(.*)n") but only the first "270" is extracted, I need help, searched through all forums, couldn't get exact match, it will save me huge time if anyone could give me some hint on how to derive the sum inside same cell string
regex google-sheets excel-formula google-sheets-formula
edited Mar 8 at 0:25
player0
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
you want that in google sheets?
– player0
Mar 7 at 23:17
add a comment |
AL-CHE-P1-1518 --- 270
AL-CHE-P2-1318 --- 280
AL-MAT-P1-1218 --- 280
AL-MAT-P4-0918 --- 40
all these data are inside same cell C2, my aim is to derive a formula to sum
270+280+280+40
in cell D2
tried regextract(c2,"d(.*)n") but only the first "270" is extracted, I need help, searched through all forums, couldn't get exact match, it will save me huge time if anyone could give me some hint on how to derive the sum inside same cell string
regex google-sheets excel-formula google-sheets-formula
edited Mar 8 at 0:25
player0
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
AL-CHE-P1-1518 --- 270
AL-CHE-P2-1318 --- 280
AL-MAT-P1-1218 --- 280
AL-MAT-P4-0918 --- 40
all these data are inside same cell C2, my aim is to derive a formula to sum
270+280+280+40
in cell D2
tried regextract(c2,"d(.*)n") but only the first "270" is extracted, I need help, searched through all forums, couldn't get exact match, it will save me huge time if anyone could give me some hint on how to derive the sum inside same cell string
regex google-sheets excel-formula google-sheets-formula
regex google-sheets excel-formula google-sheets-formula
edited Mar 8 at 0:25
player0
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
edited Mar 8 at 0:25
player0
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
edited Mar 8 at 0:25
player0
6,87541232
edited Mar 8 at 0:25
player0
6,87541232
edited Mar 8 at 0:25
player0
6,87541232
6,87541232
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
asked Mar 7 at 22:40
vampirekabirvampirekabir
186
186
you want that in google sheets?
– player0
Mar 7 at 23:17
add a comment |
you want that in google sheets?
– player0
Mar 7 at 23:17
you want that in google sheets?
– player0
Mar 7 at 23:17
you want that in google sheets?
– player0
Mar 7 at 23:17
add a comment |
3 Answers
3
active
oldest
votes
=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(SPLIT(C2,CHAR(10))," d+")))
answered Mar 7 at 23:25
player0player0
6,87541232
add a comment |
As far as I know, you can only accomplish this via a UDF:
Function ReturnSum(rng As Range) As Long
Dim arr As Variant
arr = Split(rng.Value, Chr(10) & Chr(10))
For i = 0 To UBound(arr)
ReturnSum = ReturnSum + Trim(Split(arr(i), " --- ")(1))
Next i
End Function
answered Mar 7 at 23:12
dwironydwirony
4,64631434
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
add a comment |
In Excel the formula is a bit more complicated and an array formula:
=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55053969%2fexcel-sum-values-by-extracting-numbers-from-single-multi-line-cell%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(SPLIT(C2,CHAR(10))," d+")))
answered Mar 7 at 23:25
player0player0
6,87541232
add a comment |
=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(SPLIT(C2,CHAR(10))," d+")))
answered Mar 7 at 23:25
player0player0
6,87541232
add a comment |
=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(SPLIT(C2,CHAR(10))," d+")))
answered Mar 7 at 23:25
player0player0
6,87541232
=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(SPLIT(C2,CHAR(10))," d+")))
answered Mar 7 at 23:25
player0player0
6,87541232
answered Mar 7 at 23:25
player0player0
6,87541232
answered Mar 7 at 23:25
player0player0
6,87541232
answered Mar 7 at 23:25
player0player0
6,87541232
6,87541232
add a comment |
add a comment |
As far as I know, you can only accomplish this via a UDF:
Function ReturnSum(rng As Range) As Long
Dim arr As Variant
arr = Split(rng.Value, Chr(10) & Chr(10))
For i = 0 To UBound(arr)
ReturnSum = ReturnSum + Trim(Split(arr(i), " --- ")(1))
Next i
End Function
answered Mar 7 at 23:12
dwironydwirony
4,64631434
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
add a comment |
As far as I know, you can only accomplish this via a UDF:
Function ReturnSum(rng As Range) As Long
Dim arr As Variant
arr = Split(rng.Value, Chr(10) & Chr(10))
For i = 0 To UBound(arr)
ReturnSum = ReturnSum + Trim(Split(arr(i), " --- ")(1))
Next i
End Function
answered Mar 7 at 23:12
dwironydwirony
4,64631434
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
add a comment |
As far as I know, you can only accomplish this via a UDF:
Function ReturnSum(rng As Range) As Long
Dim arr As Variant
arr = Split(rng.Value, Chr(10) & Chr(10))
For i = 0 To UBound(arr)
ReturnSum = ReturnSum + Trim(Split(arr(i), " --- ")(1))
Next i
End Function
answered Mar 7 at 23:12
dwironydwirony
4,64631434
As far as I know, you can only accomplish this via a UDF:
Function ReturnSum(rng As Range) As Long
Dim arr As Variant
arr = Split(rng.Value, Chr(10) & Chr(10))
For i = 0 To UBound(arr)
ReturnSum = ReturnSum + Trim(Split(arr(i), " --- ")(1))
Next i
End Function
answered Mar 7 at 23:12
dwironydwirony
4,64631434
answered Mar 7 at 23:12
dwironydwirony
4,64631434
answered Mar 7 at 23:12
dwironydwirony
4,64631434
answered Mar 7 at 23:12
dwironydwirony
4,64631434
4,64631434
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
add a comment |
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
Wanna bet that the only way is UDF in Excel?
– Scott Craner
Mar 7 at 23:25
add a comment |
In Excel the formula is a bit more complicated and an array formula:
=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
add a comment |
In Excel the formula is a bit more complicated and an array formula:
=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
add a comment |
In Excel the formula is a bit more complicated and an array formula:
=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
In Excel the formula is a bit more complicated and an array formula:
=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
answered Mar 7 at 23:34
Scott CranerScott Craner
93.5k82652
93.5k82652
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
add a comment |
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
Yet again, black magic... I imagine there are very few people around that can write a formula to achieve this!
– dwirony
Mar 8 at 16:02
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55053969%2fexcel-sum-values-by-extracting-numbers-from-single-multi-line-cell%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you want that in google sheets?
– player0
Mar 7 at 23:17