in Excel I have a few cells containing numbers sperated bij a dash, like:
I use a formula like
=TRIM(TEKSTBEFORE(G30:G40;"/";1))
>Solution :
Try using TEXTSPLIT()
• Formula used in cell B1
=SUM(--TEXTSPLIT(A1," / "))
Or,
=MAP(A1:A7,LAMBDA(φ, SUM(--TEXTSPLIT(φ," / "))))
Edit: As OP Commented:
Sorry, I think I’m not clear enough. I have to sum all first values,
all second values and all thirth values separately. In this example
the result would be 4 / 98 / 0. That would be the end result. Thanks
for the reply b.t.w
• Formula used in cell B1
=TEXTJOIN(" / ",,BYCOL(TEXTSPLIT(TEXTAFTER(" / "&A1:A7," / ",{1,2,3})," / "),LAMBDA(α, SUM(--α))))
Also in **Excel Beta Version you can use GROUPBY()
=GROUPBY(,--TEXTSPLIT(TEXTAFTER(" / "&A1:A9," / ",{1,2,3})," / "),SUM)
Or using PIVOTBY()
=PIVOTBY(,,--TEXTSPLIT(TEXTAFTER(" / "&A1:A9," / ",{1,2,3})," / "),SUM)
To know more on the New Launched function in Beta Versions of Windows Excel & Mac can be found here: