# Ignore data within parentheses from formula



## Niall19 (Dec 13, 2022)

Hello,

So, I'm quite the novice when it comes to excel formulae so any help would be greatly appreciated. This is probably very straightforward but I would like "Remaining volume" in cell "G3" to substract the values in "J3", "K3" and "L3" from the value in "I3", crucially, ignoring the data within the parentheses. Is this possible and if so, could a kind soul offer some advice on how to achieve this? 

REMAINING VOLUME (µL)INITIAL QUANTITY RECEIVED (mg)INITIAL QUANTITY (µL)QUANTITY USED (µL)QUANTITY USED (µL)QUANTITY USED (µL)_SUM of initial quantity minus all quantity used, ignoring parentheses._10000​10000​100 (19SEP22)127 (23SEP22)120 (26SEP22)G3^​H3^​I3^​J3^​                K3^           L3 ^​​

Kind regards,
Niall


----------



## Fluff (Dec 13, 2022)

Hi & welcome to MrExcel.
How about
Fluff.xlsmGHIJKL12REMAINING VOLUME (µL)INITIAL QUANTITY RECEIVED (mg)INITIAL QUANTITY (µL)QUANTITY USED (µL)QUANTITY USED (µL)QUANTITY USED (µL)396531000010000100 (19SEP22)127 (23SEP22)120 (26SEP22)DataCell FormulasRangeFormulaG3G3=I3-SUM(--TEXTSPLIT(J3:L3,"("))


----------



## Niall19 (Dec 13, 2022)

Fluff said:


> Hi & welcome to MrExcel.
> How about
> Fluff.xlsmGHIJKL12REMAINING VOLUME (µL)INITIAL QUANTITY RECEIVED (mg)INITIAL QUANTITY (µL)QUANTITY USED (µL)QUANTITY USED (µL)QUANTITY USED (µL)396531000010000100 (19SEP22)127 (23SEP22)120 (26SEP22)DataCell FormulasRangeFormulaG3G3=I3-SUM(--TEXTSPLIT(J3:L3,"("))


Hi,
Thank you for the assistance! I've tried this formula but unfortunately it's throwing up a "#NAME?" error. I'm sure this is something I am doing wrong. Any ideas as to what that might be?

Niall


----------



## fjns (Dec 13, 2022)

Hi, try this formula for G3

=I3-IF(IFERROR(SEARCH("(",J3),0)=0,J3,LEFT(J3,SEARCH("(",J3)-1))-IF(IFERROR(SEARCH("(",K3),0)=0,K3,LEFT(K3,SEARCH("(",K3)-1))-IF(IFERROR(SEARCH("(",L3),0)=0,L3,LEFT(L3,SEARCH("(",L3)-1))


----------



## Fluff (Dec 13, 2022)

Niall19 said:


> I'm sure this is something I am doing wrong


Nope, you probably don't have the Textsplit function yet.
How about
	
	
	
	
	
	



```
=I3-SUM(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(J3:L3," ","</m><m>"))&"</m></k>","//m"))
```


----------



## Niall19 (Dec 13, 2022)

Fluff said:


> Nope, you probably don't have the Textsplit function yet.
> How about
> 
> 
> ...


This is really cool! Thank you so much for this solution. 

Take care!


----------



## Fluff (Dec 13, 2022)

Glad we could help & thanks for the feedback.


----------



## Niall19 (Dec 16, 2022)

Fluff said:


> Glad we could help & thanks for the feedback.


Just one more thing which would be great to find a solution for, not a requirement but it would tidy up the appearance of the spreadsheet - The formula/column "G" cells which are blank (no entries made yet) are showing up the "#VALUE!" error message.

Would there be a way to write something into the formula to show these cells as blank or 0 when they haven't yet been filled in, but still have the formula applied in advance? Sorry for another novice level question!

Kind regards,
Niall


----------



## Sufiyan97 (Dec 16, 2022)

Try


```
=IFERROR(I3-SUM(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(J3:L3," ","</m><m>"))&"</m></k>","//m")),"")
```


----------



## fjns (Dec 16, 2022)

Hi, try this formula for G3

=IF(OR(ISBLANK(I3),ISBLANK(J3),ISBLANK(K3),ISBLANK(L3)),0,I3-IF(IFERROR(SEARCH("(",J3),0)=0,J3,LEFT(J3,SEARCH("(",J3)-1))-IF(IFERROR(SEARCH("(",K3),0)=0,K3,LEFT(K3,SEARCH("(",K3)-1))-IF(IFERROR(SEARCH("(",L3),0)=0,L3,LEFT(L3,SEARCH("(",L3)-1)))


----------



## Niall19 (Dec 13, 2022)

Hello,

So, I'm quite the novice when it comes to excel formulae so any help would be greatly appreciated. This is probably very straightforward but I would like "Remaining volume" in cell "G3" to substract the values in "J3", "K3" and "L3" from the value in "I3", crucially, ignoring the data within the parentheses. Is this possible and if so, could a kind soul offer some advice on how to achieve this? 

REMAINING VOLUME (µL)INITIAL QUANTITY RECEIVED (mg)INITIAL QUANTITY (µL)QUANTITY USED (µL)QUANTITY USED (µL)QUANTITY USED (µL)_SUM of initial quantity minus all quantity used, ignoring parentheses._10000​10000​100 (19SEP22)127 (23SEP22)120 (26SEP22)G3^​H3^​I3^​J3^​                K3^           L3 ^​​

Kind regards,
Niall


----------



## Fluff (Dec 16, 2022)

Another option
	
	
	
	
	
	



```
=IF(COUNTIFS(J3:L3,"<>")=0,0,I3-SUM(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(J3:L3," ","</m><m>"))&"</m></k>","//m")))
```


----------

