HomePro
Board Regular
- Joined
- Aug 3, 2021
- Messages
- 157
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Prefer Not To Say
- Platform
- Windows
- MacOS
- Mobile
- Web
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?
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?