sum a value(substring) in a cell

It would be far better if the data were already separated into columns.

Regards --
This is known as the double unary operator and is used to coerce a number stored as text to a number, ie --"10" becomes 10

Regards CTRL + SHIFT + ENTER
This is an Array formula and is often referred to as a CSE formula ... see the link in my sig to Colin_L's Array tutorial/discussion for a more in depth review on the subject.

You could possibly revert to a SUMPRODUCT (normal ENTER entry) over a SUM CSE, eg the below mimics the previously detailed CSE:

=SUMPRODUCT(--(LEFT(A1:A4,FIND("|",A1:A4)-1)))

however both the CSE & the above SUMPRODUCT are in their present form are however open to error if any cell within A1:A4 fails to contain the pipe symbol and/or entry contains alpha text only.
In this regard the previously detailed SUMPRODUCT could be strengthened slightly to:

=SUMPRODUCT(--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))

However should the range contain non-numerics pre pipe then it would still generate an error.

Or you can select your data and DATA->TEXT TO COLUMNS-> delimited -> in other write |

And you'll get separated data for numbers, dates and data

This formula will inore the text and sum only number on left side of pipe symbol
Don't forget to press CSE (Ctril + Shift + Enter)

{=SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0))}

I would opt for:

=SUMPRODUCT(--(RIGHT(A1:A10,5)="|here"),--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))

Originally Posted by DonkeyOte

I would opt for:

=SUMPRODUCT(--(RIGHT(A1:A10,5)="|here"),--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))

I came to smiliar...

=SUMPRODUCT(--(RIGHT(A1:A8;4)="here");1*(LEFT(A1:A8;FIND("|";A1:A8;1)-1)))

of course, better is to look for |here but I was wondering what 0"&A1:A10 doing?

The 2nd result can be achieved with this .... But i think Sumproduct is good approach if you have some knowledge about it .......

Don't forget to press CSE (Ctril + Shift + Enter)

=SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here")))

Originally Posted by zbor

I came to smiliar...

=SUMPRODUCT(--(RIGHT(A1:A8;4)="here");1*(LEFT(A1:A8;FIND("|";A1:A8;1)-1)))

of course, better is to look for |here but I was wondering what 0"&A1:A10 doing?

I think this is to control error that if you have nothing on left side of "|" then your formula will give error .. what you think DK ????

To handle blanks...ie blanks become "0|"
(so you can apply the formula to a range containing blanks be they at the end or interspersed amongst other entries)

If you read the earlier posts all methods would still fail if a given cell in the range was non-blank but failed to contain a number in the left most section (ie "aaa") ... this could be accounted for but is most likely not required.

Originally Posted by DonkeyOte

If you read the earlier posts all methods would still fail if a given cell in the range was non-blank but failed to contain a number in the left most section (ie "aaa") ... .

DK but my formula will always work even there will be no "|" or blank cell .....

Yes it would, apologies mubashir - I should have reviewed more thoroughly before casting dispersions

A more powerfull approach if there is no match found ...

Don't forget to press (CSE)

=IF(SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here")))=0,"No Match Found",SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here"))))

@ DK No problem as you are one of those persons from forum bcoz of them I'm learning a lot …..