Column 1 and Column 2

[vmjan02]

I have this data in excel but tried a lot not sure as how to get output1 and output2, is there a formula or vba to get it.

Cisco-INTMAC.csv
	A	B	C	D	E	F
11	Name	Mac Address Type Learned from/Filtered on LC learned Resync Age/Last Change Mapped to			OutPut 1	OutPut 2
12	PE-Agg10-Abq-321-2	c4ff.1fad.69c4 dynamic BE110.3815 N/A 24 Nov 14:00:42 N/A			c4ff.1fad.69c4	BE110
13	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 68 N/A N/A N/A
14	PE-Agg10-Abq-321-2	001d.b528.67c8 dynamic (192.168.182.19	287200) N/A 24 Nov 14:00:42 N/A
15	PE-Agg10-Abq-321-2	7079.b396.178d dynamic Gi102/0/0/14.634 N/A 24 Nov 14:01:42 N/A
16	PE-Agg10-Abq-321-2	001d.b529.3fc2 dynamic (192.168.182.18	22224600) N/A 24 Nov 14:01:12 N/A
17	PE-Agg10-Abq-321-2	c84f.8603.e369 dynamic Gi102/0/0/14.3341 N/A 24 Nov 14:01:12 N/A
18	PE-Agg10-Abq-321-2	3458.4008.d0ae dynamic (192.168.115.194	N/A 24 Nov 14:00:12 N/A 46070100)
19	PE-Agg10-Abq-321-2	b414.897a.e642 dynamic BE40.17 N/A 24 Nov 14:01:42 N/A			b414.897a.e642	BE40
20	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 230 N/A N/A N/A
21	PE-Agg10-Abq-321-2	0019.8f2e.ed11 dynamic BE10.39 N/A 24 Nov 14:01:42 N/A			0019.8f2e.ed11	BE10
22	PE-Agg10-Abq-321-2	54a6.1935.af6f dynamic BE10.39 N/A 24 Nov 14:00:12 N/A			54a6.1935.af6f	BE10
23	PE-Agg10-Abq-321-2	0007.724f.7fbb dynamic BE70.39 N/A 24 Nov 14:02:12 N/A			0007.724f.7fbb	BE70
24	PE-Agg10-Abq-321-2	0019.8fa4.81f7 dynamic BE70.39 N/A 24 Nov 14:02:12 N/A			0019.8fa4.81f7	BE70
25	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 231 N/A N/A N/A
26	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 232 N/A N/A N/A
27	PE-Agg10-Abq-321-2	0001.47cf.82aa dynamic Gi100/0/0/7.92 N/A 24 Nov 14:02:12 N/A
28	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 233 N/A N/A N/A
29	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 234 N/A N/A N/A
30	PE-Agg10-Abq-321-2	b414.897a.e647 dynamic BE40.250 N/A 24 Nov 14:02:12 N/A			b414.897a.e647	BE40
31	PE-Agg10-Abq-321-2	4cb1.6cf9.1920 dynamic Gi102/0/0/3.250 N/A 24 Nov 14:01:12 N/A
32	PE-Agg10-Abq-321-2	286e.d475.7e90 dynamic Gi102/0/0/5.250 N/A 24 Nov 14:01:42 N/A
33	PE-Agg10-Abq-321-2	6c41.0e50.682a routed BD id: 235 N/A N/A N/A
34	PE-Agg10-Abq-321-2	4482.e58d.0bb0 dynamic BE80.3029 N/A 24 Nov 14:01:42 N/A			4482.e58d.0bb0	BE80
35	PE-Agg10-Abq-321-2	5c4c.a915.8da7 dynamic BE80.3029 N/A 24 Nov 14:00:42 N/A			5c4c.a915.8da7	BE80
36	PE-Agg10-Abq-321-2	5c4c.a916.eaa3 dynamic BE80.3029 N/A 24 Nov 14:00:12 N/A			5c4c.a916.eaa3	BE80
Cisco-INTMAC

 

[awoohaw]

Can you describe the rules needed to get the outputs?

 

[vmjan02]

Can you describe the rules needed to get the outputs?

from column B need to check BE00 is avaliable if yes then from that row need to get the output 1 and output 2.
so in eg row no 12 it has BE110.38 so in Output 2 it will be BE110 and in Output 1 it iwll be c4ff.1fad.69c4

 

[Scott Huish]

Formula in E2:
=IF(ISERROR(FIND(" BE",B2)),"",TEXTBEFORE(B2," "))
Formula in F2:
=IF(E2="","",CHOOSECOLS(TEXTSPLIT(B2," "),3))

Copy down.

 

[vmjan02]

this is perfcet by any change the result for

Formula in F2:
=IF(E2="","",CHOOSECOLS(TEXTSPLIT(B2," "),3)) is comming as BE110.3815 can it be BE110

 

[Scott Huish]

=IF(E2="","",TEXTBEFORE(CHOOSECOLS(TEXTSPLIT(B2," "),3),"."))