Hello,
Could someone please help me with the IF statement in column Q? The formula should be If Column J is "Y" then remove substring "HM" from column P. The formula I'm trying is also deleting everything after the substring and if the substring doesn't exist then it's giving me #VALUE!
Could someone please help me with the IF statement in column Q? The formula should be If Column J is "Y" then remove substring "HM" from column P. The formula I'm trying is also deleting everything after the substring and if the substring doesn't exist then it's giving me #VALUE!
| APCI Admin Fee D PPA - 05.15.22 Detail.xlsx | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | YearMonth | Date | Account # | Concatenate (Date & Account #) | BPR | GPR | Group BPR | Group GPR | GCR | HM | Waiver | Missed Criteria | District | SFDC Last Modified (Approval Date) | Status | SFDC Waivers | Formula | Expected Result | ||
| 2 | 202201 | 1/1/2022 | 514588 | 44562514588 | 95.450 | 89.370 | 15.530 | Y | GPR | 101100 | Apr-22 | FALSE | GPR HM | GPR | GPR | |||||
| 3 | 202201 | 1/1/2022 | 995583 | 44562995583 | 75.000 | 75.000 | 15.090 | Y | BPR GPR | 101100 | Apr-22 | FALSE | BPR HM GPR | BPR | BPR GPR | |||||
| 4 | 202202 | 2/1/2022 | 765629 | 44593765629 | 97.090 | 86.000 | 0.000 | Y | GPR | 101100 | Apr-22 | FALSE | BPR | #VALUE! | BPR | |||||
| 5 | 202202 | 2/1/2022 | 765624 | 44593765624 | 87.000 | 87.000 | 0.000 | N | GPR | 101100 | Apr-22 | FALSE | BPR GPR HM | BPR GPR HM | BPR GPR HM | |||||
Sheet1 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q2:Q5 | Q2 | =IF(J2="Y", LEFT(P2,SEARCH("HM",P2)-1), P2) |
