Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello All,
I have a formula that is working well but now that I have moved deeper into my project I noticed that there is one scenario that the formula currently doesn't account for and I was hoping someone might know a way to adapt a modification to my formula.
Current Formula: =IFERROR(IF(ISNUMBER(SEARCH("V",K2)),TRIM(MID(K2,SEARCH("V",K2),12)),TRIM(MID(L2,SEARCH("V",L2),12))),VLOOKUP(J2,A:B,2,0))
Formula Breakdown:
1. IF(ISNUMBER(SEARCH("V",K2)) - Explanation: If my range K2 contains "V" then Look at column K, if not Look at Column L
2. TRIM(MID(K2,SEARCH("V",K2),12)) - Explanation: Using Cell K2, Find the Location of the character "V" and use that as a starting position and extract 12 characters to the right
3. TRIM(MID(L2,SEARCH("V",L2),12))) - Explanation: If no "V" is found in K2, then Look in L2 for "V" and extract the 12 characters from this cell
4. VLOOKUP(J2,A:B,2,0)) - Explanation: This is part of the IFERROR Statement at the beginning of the formula, if neither Column K or L contain the letter "V" then lookup a value from a mapping table.
Modifcation needed (within the "ISNUMBER(SEARCH"): IF(K2 Contains "V" and the last two characters of the 12 needed end with "01" then extract, if no "V" is found in column K, then try the same thing in Column L.
So, its really just the validation piece of when the letter "V" is found, I need to validate that the last two character of the 12 character string end in "01" if not let the Vlookup do the work.
Sample String: P#6000346874, LINE 10, I/O#VKEOT0560101, KERR EHR - OTD
Result Needed: VKEOTO560101
New Scenario Sample: 01001 - OVERALL TERM DEAL
Result Needed: Use the vlookup because there is a "V" in the string but it does not end in "01" if you extracted 12 characters
Currently getting this: "VERALL TERM"
Any help is appreciated! ?
I have a formula that is working well but now that I have moved deeper into my project I noticed that there is one scenario that the formula currently doesn't account for and I was hoping someone might know a way to adapt a modification to my formula.
Current Formula: =IFERROR(IF(ISNUMBER(SEARCH("V",K2)),TRIM(MID(K2,SEARCH("V",K2),12)),TRIM(MID(L2,SEARCH("V",L2),12))),VLOOKUP(J2,A:B,2,0))
Formula Breakdown:
1. IF(ISNUMBER(SEARCH("V",K2)) - Explanation: If my range K2 contains "V" then Look at column K, if not Look at Column L
2. TRIM(MID(K2,SEARCH("V",K2),12)) - Explanation: Using Cell K2, Find the Location of the character "V" and use that as a starting position and extract 12 characters to the right
3. TRIM(MID(L2,SEARCH("V",L2),12))) - Explanation: If no "V" is found in K2, then Look in L2 for "V" and extract the 12 characters from this cell
4. VLOOKUP(J2,A:B,2,0)) - Explanation: This is part of the IFERROR Statement at the beginning of the formula, if neither Column K or L contain the letter "V" then lookup a value from a mapping table.
Modifcation needed (within the "ISNUMBER(SEARCH"): IF(K2 Contains "V" and the last two characters of the 12 needed end with "01" then extract, if no "V" is found in column K, then try the same thing in Column L.
So, its really just the validation piece of when the letter "V" is found, I need to validate that the last two character of the 12 character string end in "01" if not let the Vlookup do the work.
Sample String: P#6000346874, LINE 10, I/O#VKEOT0560101, KERR EHR - OTD
Result Needed: VKEOTO560101
New Scenario Sample: 01001 - OVERALL TERM DEAL
Result Needed: Use the vlookup because there is a "V" in the string but it does not end in "01" if you extracted 12 characters
Currently getting this: "VERALL TERM"
Any help is appreciated! ?