Formula for sum of values separated by special character

[TPR]

Hi Friends,
My cells are carrying figures like the following:
1515.25~~ 5774.05~~ 375475.16~~ 2171.32.
Suppose it is in A10, I badly need a formula in B10 which will give me result 384935.78.

Please note that this type of figures are not in all cells. There are lot of cells which are carrying normal values. Pls help me providing me formula. Pls don't give VBA.

Thanks in advance.

Regards
TPR

 

[Hui]

I will assume the trailing "." was a typo as you typed the question
In that case use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

If it is meant to have a trailing "." use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B10,LEN(B10)-1)," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

 

[TPR]

I will assume the trailing "." was a typo as you typed the question
In that case use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))
If it is meant to have a trailing "." use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B10,LEN(B10)-1)," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

Hello,
Yes "." was typo. Your were right.
Your formula is perfect. But how can I learn Sir. I want to know how the formula works ? is it the perfect place to learn ?
Regards
TPR

 

[pecoflyer]

Or use EXCEL'S MYSTERY FUNCTION "EVALUATE"
First open the name manager and enter a name of your choice ( say RESULT)
In the "refers to" enter =EVALUATE(SUBSTITUTE(Sheet1!$A$10,"~~","+"))
When you now enter =RESULT in B10, you get your answer
Do not forget to save your file as xlsm, NOT xlsx

 

[TPR]

I will assume the trailing "." was a typo as you typed the question
In that case use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

If it is meant to have a trailing "." use:
=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B10,LEN(B10)-1)," ",""),"~","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

Sir,
Can you pls help me how would I get highest value in between those ? Desired Result is 375475.16

 

[TPR]

Or use EXCEL'S MYSTERY FUNCTION "EVALUATE"
First open the name manager and enter a name of your choice ( say RESULT)
In the "refers to" enter =EVALUATE(SUBSTITUTE(Sheet1!$A$10,"~~","+"))
When you now enter =RESULT in B10, you get your answer
Do not forget to save your file as xlsm, NOT xlsx

Thanks Friend, it also works but be very precise Hui's formula is not forcing me too many entries.

 

[Khalid NGO]

Hi,

Recently used FILTERXML function with SUBSTITUTE:

=SUMPRODUCT(FILTERXML("<a><b>"&SUBSTITUTE(A10,"~~ ","</b><b>")&"</b></a>","//b"))

Regards,

 

[Khalid NGO]

how would I get highest value in between those ? Desired Result is 375475.16

Hi again,

Try this:
=AGGREGATE(14,6,FILTERXML("<a><b>"&SUBSTITUTE(A10,"~~ ","</b><b>")&"</b></a>","//b"),1)

Regards,