Extract portion of cell contents based on another cell

[slam]

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:

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!

 

[mamady]

Hi slam,

Please check the below:

Book1
	A	B	C	D	E	F	G	H	I	J
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
Sheet2

Cell Formulas
Range	Formula
H2:J7	H2	=IFERROR(MID(A2,1,FIND($D2,A2)-3)*1,"")

Regards

 

[slam]

Hi mamady,

Thank you so much for the response.

This seems to be only displaying the long number when the match is with the first code, but not with any subsequent number/code. Perhaps this is because I didn't have a good example of this above.

For instance, using your cell references.....
if A2 contained: 4434343 (CC11), 343433334333 (JJ11), 33439343 (JU09)
and if in D2, the code was: JU09
I would want 33439343 displayed in H2

Thank you!

 

[jtakw]

Hi,

Use O2 formula copied down and across for results as Text.
Use R2 formula for results converted to Real Numbers, Not recommended if you may have leading 0 (zeros).

Book3.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Reg1	Reg2	Reg3	Code				Reg1 #	Reg2 #	Reg3 #
2	4434343 (CC11), 343433334333 (JJ11), 33439343 (JU09)	123456 (AB01), 12345678 (AB02)	N/A	AB01					123456			123456
3	N/A	12345678 (AB02)		AB02					12345678			12345678
4	N/A		3212333 (AB03), 34343 (CH02)	AB03						3212333			3212333
5	(Empty)	Requested	(Empty)	AB03
6	123456789 (BC01)		(Empty)	BC01				123456789			123456789
7	(Empty)	2334333 (AB02)	3434333 (ZZ11), 3433343 (CC12)	AB02					2334333			2334333
8	4434343 (CC11), 343433334333 (JJ11), 33439343 (JU09)			JU09				33439343			33439343
Sheet801

Cell Formulas
Range	Formula
O2:Q8	O2	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(H2,SEARCH($K2,H2)-2),",",REPT(" ",100)),100)),"")
R2:T8	R2	=IFERROR(RIGHT(SUBSTITUTE(LEFT(H2,SEARCH($K2,H2)-2),",",REPT(" ",100)),100)+0,"")

 

[slam]

Hi jtakw, the O2 formula is perfect. Thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.

I made a slight change to my formulas a minute after I posted, just make sure you're using what's showing now. Thanks.