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, how about

=IFERROR(TRIM(RIGHT(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]

I think thats it! still some errors but I think I need to clean up my Criteria.
Your the best!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[HomePro]

I think thats it! still some errors but I think I need to clean up my Criteria.
Your the best!

Ok I see the issue. If the $ amount is negative it does not bring across and value.

Appointment_General_Data.xlsx
																																																																									BU																																DA	DB	DC	DD	DE	DF	DG
1																																																																									Pricing_Attributes																																4 Point	Additional Price	Air Sampling	Asbestos Testing	Condominium	Convenience Fee	Discount Code
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
5																																																																									WDO Additional Visit - FL ONLY - Primo Pest ($115.00)
6																																																																									4 Point Insurance Evaluation ($157.00) | Discount Code JWB4PT applied ($-82.00)																																157
7																																																																									4 Point Insurance Evaluation ($157.00) | Discount Code JWB4PT applied ($82.00)																																157						82
Sheet1

Cell Formulas
Range	Formula
DA2:DG7	DA2	=IFERROR(TRIM(RIGHT(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]

Ok, how about

=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]

It scares me when you say "How About" the first thought is - he's tinkering to see if it might work!
But then I remember its Fluff - the god of code. And clearly it works.
Im sure its not said enough - your a godsend.
Thank you.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[HomePro]

actually ran into one issue. after I run the sheet I am copy pasting the cells as values so the data wont change. And when I try to add up cells that include blank cells I get a value error. guess the formula leaves the cells as null. how can I work around this or modify the formula to populate "0"?

 

[Fluff]

How are you trying to add them?

 

[HomePro]

that reminded me --- i can use sum. sry to trouble you.