Combining formulas

[HomePro]

I am trying to simplify an existing workbook.
I have almost 1000 lines and over 50 headers so reviewing it is an eyesore.

Right now I am creating two columns. the first formula parses the entry from a data cell that looks like:
Single-Family Home (Residential Inspection) ($23.60) | 4 Point Insurance Evaluation ($0.00) | WDO Inspection - FL ONLY - Primo ($50.00)
So if the column header is wdo the cell populates " WDO Inspection - FL ONLY - Primo ($50.00)"
and then the second parses the $ value so the results are 50.00.

Is there anyway I can combine these two formulas into one?
=IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE($E3,1,SEARCH(G$1,$E3)-1,""),"|",REPT(" ",999)),999)),"")
and
=IFERROR(SUBSTITUTE(REPLACE(G3,1,SEARCH("$",G3),""),")","")+0,"")

If I could get the 50 result and remove 50 columns it would be so much easier.

any ideas?

 

[Fluff]

Ok

 

[Dave Patton]

In the off chance that the following helps.
The "-" coerces the text in brackets to a positive number.

Single-Family Home (Residential Inspection) ($23.60) | 4 Point Insurance Evaluation ($0.00) | WDO Inspection - FL ONLY - Primo ($50.00)	($50.00)	50.00​
Single-Family Home (Residential Inspection) ($23.60) | 4 Point Insurance Evaluation ($0.00) | WDO Inspection - FL ONLY - Primo ($999.99)	($999.99)	999.99​
	0.00​	1,049.99​

 

[HomePro]

tks

 

[HomePro]

OMG. Thought I was done with this..... Seems that for some entries the formula is posting a 0 that is creating me issues elsewhere.
Anyway around this, can we just have it print ""? [blank like the adjacent cells?]

Appointment_General_Data.xlsx
	BU																																DA	DB	DC	DD	DE	DF
1	Pricing_Attributes																																4 Point	Additional Price	Air Sampling	Asbestos Testing	Condominium	Convenience Fee
2	Single-Family Home (Residential Inspection) ($1323.60) | 4 Point Insurance Evaluation ($0.00) | WDO Inspection - FL ONLY - Primo Pest ($0.00) | Wind Mitigation Assessment ($0.00) | Florida Package ($297.00) | Convenience Fee ($56.72)																																0					56.72
3	Single-Family Home (Phase 3 - Mattamy - New Construction: Final Inspection) ($424.00) | Sewer Line Inspection ($223.00) | Convenience Fee ($22.65)																																					22.65
4	WDO Inspection - FL ONLY - Primo Pest ($197.00) | Convenience Fee ($6.90)																																					6.9
Sheet1

 

[Fluff]

With xl 2016 you need to double up on the formula like

=IF(IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,"")=0,"",IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,""))

 

[HomePro]

That is just so Beyond me. Happy your here to help!
Thanks again

 

[Fluff]

My Pleasure.

 

[HomePro]

Hey Fluff,
Hope your still watching this.
Below is your code that I am using. If it does not find a response the cell is left null.
Can we make it so it populates a 0 instead of "".
I tried but could not get to to work

eds.xlsx
	DA	DB	DC
1	4 Point	Additional Price	Air Sampling
2		15
3
4
5
Sheet1

Cell Formulas
Range	Formula
DA2:DC5	DA2	=IF(IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,"")=0,"",IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,""))

 

[Fluff]

Try changing the final "" to 0

 

[HomePro]

almost worked - still leaves a few ""

eds.xlsx
	DA	DB	DC	DD
1	4 Point	Additional Price	Air Sampling	Asbestos Testing
2	0	15	0	0
3	0	0	0	0
4		0	0	0
5		0	0	0
6	0	0	0	0
7	85	10	0	0
8	157	0	0	0
9	157	0	0	0
Sheet1

Cell Formulas
Range	Formula
DA2:DD9	DA2	=IF(IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,"")=0,"",IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,""),FIND("|",REPLACE($BU2,1,SEARCH(DA$1,$BU2)-1,"")&" |")-1,LEN($BU2),""),"$",""),"(",""),")","")," ",REPT(" ",100)),100))*1,0))