Hi all,
I have 3 columns, H, I, J with numbers (varying length) followed by a code in parenthesis. If there's multiple numbers/codes, they're separated by a comma then a space. At most, there could be 10 instances of numbers/codes, but there could also be none, or text in the cell.
Then I have a code in K which matches just one of the codes in one of the three columns.
What I'm trying to do is extract only the number before the parenthesis in to the corresponding columns O, P, Q, where the part in parenthesis matches what's in Column K. So it's O, P, Q that I'm looking to automate with a formula. I think this is a good example of various data scenarios and what I'm trying to achieve as an end result:
Any help would be greatly appreciated!
I have 3 columns, H, I, J with numbers (varying length) followed by a code in parenthesis. If there's multiple numbers/codes, they're separated by a comma then a space. At most, there could be 10 instances of numbers/codes, but there could also be none, or text in the cell.
Then I have a code in K which matches just one of the codes in one of the three columns.
What I'm trying to do is extract only the number before the parenthesis in to the corresponding columns O, P, Q, where the part in parenthesis matches what's in Column K. So it's O, P, Q that I'm looking to automate with a formula. I think this is a good example of various data scenarios and what I'm trying to achieve as an end result:
P10-Time-02.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | N | O | P | Q | |||
1 | Reg1 | Reg2 | Reg3 | Code | Reg1 # | Reg2 # | Reg3 # | |||||
2 | 4434343 (CC11), 343433334333 (JJ11), 33439343 (JU09) | 123456 (AB01), 12345678 (AB02) | N/A | AB01 | 123456 | |||||||
3 | N/A | 12345678 (AB02) | AB02 | 12345678 | ||||||||
4 | N/A | 3212333 (AB03), 34343 (CH02) | AB03 | 3212333 | ||||||||
5 | (Empty) | Requested | (Empty) | AB03 | ||||||||
6 | 123456789 (BC01) | (Empty) | BC01 | 123456789 | ||||||||
7 | (Empty) | 2334333 (AB02) | 3434333 (ZZ11), 3433343 (CC12) | AB02 | 2334333 | |||||||
Sheet1 |
Any help would be greatly appreciated!