Pulling specific data from imported data

[tispivey]

Hello again,

I have a unique issue. I have the formula set to pull specific data from a file that is given to me, but I was informed that it is pulling some of the info incorrectly.

What is happening, is I am pulling a MAC address from A2, populating it into C2, then looking for any "unknown" in B2.

I ran into a problem today where another device was coming up "unknown", so it returned the wrong info.

Is there a way to add to the formula in D2, and basically add the MAC address into it, so it only returns Rental or Purchased for the specific MAC that is returned in C2?

Thank you.

1234.xlsx
	A	B	C	D
1
2	EQUIP SERIAL : PASS : 0836C93C12E0 --DOWNSTREAM RX : (-7 to 7) : 5.4 : PASS --DOWNSTREAM SNR : (30 to 50) : 42.3 : PASS --UPSTREAM TX : (36 to 50) : 41 : PASS --UPSTREAM SNR : (30 to 50) : 39.1 : PASS EQUIP SERIAL : UNKNOWN : 1CABC060CB80 CHECKED AT: 4/21/22 11:14 AM OVERALL RESULT: FAIL	SN#0836C93C12E0 ; TYPE:I; MODEL:CMODEM; OWNER CODE:P Purchased; SYS-PRIN: 8383-6300 SN#1CABC060CB80 ; TYPE:30; MODEL:AHCGN2250; OWNER CODE:R Rental/Lease; SYS-PRIN: 8383-6300	1CABC060CB80	Purchased
3
4
5
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("UNKNOWN",A2)+9,15),";",REPT(" ",99),1),99)),"")
D2	D2	=IFERROR(IF(MID(B2,SEARCH("Owner Code",B2)+11,1)="R","Rental/Lease","Purchased"),"")

 

[KRice]

Here is an idea. I noticed there are some non-printing characters in the C string that can be removed with CLEAN()...you may want to wrap the C column formula with that. I've used a separate SEARCH function to find the location in the B string where the MAC address is found (after "cleaning" the MAC address). That tells us where to begin searching the B string when we are looking for the "Owner Code". I'm assuming the Rental/Lease or Purchase details will appear soon after that search point. I modified the B cell entry to include another piece of equipment with the same MAC address (probably not possible) to confirm that the SEARCH began where expected (at the first match with the MAC address string) and returned the Owner Code details appearing soon after.

MrExcel_20220422.xlsx
	A	B	C	D
1
2	EQUIP SERIAL : PASS : 0836C93C12E0 --DOWNSTREAM RX : (-7 to 7) : 5.4 : PASS --DOWNSTREAM SNR : (30 to 50) : 42.3 : PASS --UPSTREAM TX : (36 to 50) : 41 : PASS --UPSTREAM SNR : (30 to 50) : 39.1 : PASS EQUIP SERIAL : UNKNOWN : 1CABC060CB80 CHECKED AT: 4/21/22 11:14 AM OVERALL RESULT: FAIL	SN#0836C93C12E0 ; TYPE:I; MODEL:CMODEM; OWNER CODE:P Purchased; SYS-PRIN: 8383-6300 SN#1CABC060CB80 ; TYPE:30; MODEL:AHCGN2250; OWNER CODE:R Rental/Lease; SYS-PRIN: 8383-6300 SN#1CABC060CB80 ; TYPE:30A; MODEL:AHCGN2251; OWNER CODE:Do not know; SYS-PRIN: 8383-6300	1CABC060CB80	Rental/Lease
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("UNKNOWN",A2)+9,15),";",REPT(" ",99),1),99)),"")
D2	D2	=IFERROR(IF(MID(B2,SEARCH("Owner Code",B2,SEARCH(CLEAN(C2),B2))+11,1)="R","Rental/Lease","Purchased"),"")

 

[tispivey]

This worked!

Thank you!

 

[KRice]

I'm glad to hear that...happy to help.