Help with Formula OR VBA code

[Jagat Pavasia]

dear sir,

I have Main amount figure in my A6 and want to less B6 & C6, and then answer in D6.

but my B6 and C6 have some suffix in (), so please help me.
I attached photo, please find it.


I want answer in D6 which is yellow highlighted in Imag

in example my answer is 714.35

 

[hrayani]

Hello Jagat,

One Option is like below

Book1
	A	B	C	D
5	Main Amount	Less 1	Less 2	Answer
6	800.00	25.01 (D-255)	60.64 (N-23)	714.35
Sheet1

Cell Formulas
Range	Formula
D6	D6	=A6-MID(B6,1,SEARCH("(",B6)-1)-MID(C6,1,SEARCH("(",C6)-1)

 

[Legacy 143009]

Another option could be:

=$A6-LEFT($B6,SEARCH("(",$B6)-1)-LEFT($C6,SEARCH("(",$C6)-1)

 

[Jagat Pavasia]

Hello Jagat,

One Option is like below

Book1
	A	B	C	D
5	Main Amount	Less 1	Less 2	Answer
6	800.00	25.01 (D-255)	60.64 (N-23)	714.35
Sheet1

Cell Formulas
Range	Formula
D6	D6	=A6-MID(B6,1,SEARCH("(",B6)-1)-MID(C6,1,SEARCH("(",C6)-1)

thank you sir, it is working as i need

thank you again

 

[hrayani]

thank you sir, it is working as i need

thank you again

You are welcome & thanks for the feedback

 

[Jagat Pavasia]

You are welcome & thanks for the feedback

dear sir,

If my B6 and C6 have not suffix () then it did not work,
please add something and tell me new formula.

it should be either blank, either only digit like 25.02, either with suffix like 25.02 (D-223

 

[Legacy 143009]

=$A6-IF(ISNUMBER(SEARCH("(",$B6)),LEFT($B6,SEARCH("(",$B6)-1),$B6)-IF(ISNUMBER(SEARCH("(",$C6)),LEFT($C6,SEARCH("(",$C6)-1),$C6)

 

[hrayani]

If my B6 and C6 have not suffix () then it did not work,
please add something and tell me new formula.

it should be either blank, either only digit like 25.02, either with suffix like 25.02 (D-223

You can try like @Flashbond suggested in Post # 7 or like below which is a bit shorter

=A6-IF(ISTEXT(B6),MID(B6,1,SEARCH("(",B6)-1),B6)-IF(ISTEXT(C6),MID(C6,1,SEARCH("(",C6)-1),C6)

 

[FormR]

Hi, just another option..

Book1
	A	B	C	D
5	Main Amount	Less 1	Less 2	Answer
6	800	25.01 (D-255)	60.64 (N-23)	714.35
7	800	25.01	60.64 (N-23)	714.35
8	800	25.01 (D-255)	60.64	714.35
9	800	25.01	60.64	714.35
10	800			800
11		25.01 (D-255)	60.64	-85.65
12				0
Sheet1

Cell Formulas
Range	Formula
D6:D12	D6	=A6-LEFT(B6&"0",FIND(" ",B6&" "))-LEFT(C6&"0",FIND(" ",C6&" "))

 

[Jagat Pavasia]

You can try like @Flashbond suggested in Post # 7 or like below which is a bit shorter

=A6-IF(ISTEXT(B6),MID(B6,1,SEARCH("(",B6)-1),B6)-IF(ISTEXT(C6),MID(C6,1,SEARCH("(",C6)-1),C6)

THANK YOU SO MUCH, IT IS WORKING AS MY ALL NEED