求助，怎么把EXCEL单元格中的数据提取出来

sun_sky525

MX2015007379A; SG10201703484YA; PL2935274T3; EP2935274A1; GEP20186885B; UA118021C2; BRPI1514438A2; AU2013363783A1; SI2935274T1; CN104870449A; ZA201504331A; CN104870449B; PH12015501043A1; DK2935274T3; KR1020150095692A; TWI675837B; WO2014095700A1; US9828382B2; SG11201504737RA; CA2895352A1; NZ709153A; PE10842015A1; ES2700226T3; US20140171414A1; JP2016507488A; IL239447A; HK1212985A1; TW201437210A; EA201590962A1; TN2015000276A1; EP2935274B1; AU2013363783B2; US20160251362A1; EA28507B1; CL2015001678A1; US9365577B2; RS58129B1; PH12015501043B1; PE20151084A1; JP6390919B2; IL239447D0; PT2935274T


这是EXCEL一个单元格中的数据，怎样把带有“CN”开头的数据都提取出来

kuangben8

1. Sub 提取()
   
2.     Dim Str$, arr, brr(), m%, n%
   
3.     Str = Range("A1").Value
   
4.     Str = WorksheetFunction.Substitute(Str, " ", "")
   
5.     arr = Split(Str, ";")
   
6.     ReDim brr(1 To UBound(arr) + 1, 1 To 1)
   
7.     For m = 0 To UBound(arr)
   
8.         If Left(arr(m), 2) = "CN" Then
   
9.             n = n + 1
   
10.             brr(n, 1) = arr(m)
    
11.         End If
    
12.     Next
    
13.     Range("B:B").ClearContents
    
14.     Range("B1").Resize(n, 1) = brr
    
15. End Sub

复制代码