¢ÐÄæn¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r
- ©«¤l
- 334
- ¥DÃD
- 139
- ºëµØ
- 0
- ¿n¤À
- 621
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWSXP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¤k
- ¨Ó¦Û
- ¥x¥_¿¤
- µù¥U®É¶¡
- 2010-8-5
- ³Ì«áµn¿ý
- 2022-11-3
|
¢ÐÄæn¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r
¢ÐÄæn¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r¡A½Ð°Ý¦p¦ó³]©w¤½¦¡¡AÁÂÁ¡C |
|
joyce
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-12-18
|
¦^´_ 1# leiru
ªþ¥ó¸ê®Æ§¨¬OªÅªº¡A½Ð¦A½T»{¡AÁÂÁ |
|
|
|
|
|
|
- ©«¤l
- 334
- ¥DÃD
- 139
- ºëµØ
- 0
- ¿n¤À
- 621
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWSXP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¤k
- ¨Ó¦Û
- ¥x¥_¿¤
- µù¥U®É¶¡
- 2010-8-5
- ³Ì«áµn¿ý
- 2022-11-3
|
¦^´_ 2# samwang
ÁÂÁ§iª¾,ªþ¤WÀÉ®× |
|
joyce
|
|
|
|
|
- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-12-18
|
¦^´_ 3# leiru
Sub test()
Dim Arr, xD, Reg, xE, T$, T1$, i%, j%, a
Set xD = CreateObject("Scripting.Dictionary")
Set Reg = CreateObject("VBScript.RegExp")
Arr = Range([d1], [d65536].End(3))
For i = 2 To UBound(Arr): T = Arr(i, 1): xD(T) = "": Next
Arr = Range([b1], [a65536].End(3))
For i = 2 To UBound(Arr)
T = Arr(i, 1)
With Reg
.Global = True
.IgnoreCase = True
.Pattern = "[\u4e00-\u9fa5()]+"
Set xE = .Execute(T)
a = Split(Trim(.Replace(T, "")), "¡B")
For j = 0 To UBound(a)
If Left(a(j), 2) <> "AA" Then
pos = InStr(1, a(j), "AA")
T = Mid(a(j), pos, Len(a(j)))
Else
T = a(j)
End If
If xD.Exists(T) Then: T1$ = T1$ & "¡B" & T
Next
Arr(i, 2) = Mid(T1, 2): T1 = ""
End With
Next
[a1].Resize(UBound(Arr), 2) = Arr
End Sub |
|
|
|
|
|
|
- ©«¤l
- 1391
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1401
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-12-16
|
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 334
- ¥DÃD
- 139
- ºëµØ
- 0
- ¿n¤À
- 621
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWSXP
- ³nÅ骩¥»
- OFFICE2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¤k
- ¨Ó¦Û
- ¥x¥_¿¤
- µù¥U®É¶¡
- 2010-8-5
- ³Ì«áµn¿ý
- 2022-11-3
|
¦^´_ 5# hcm19522
½Ð°Ý§Ú·Ó¤½¦¡³]©w,mark½d³òB2:E7,
¦bB2Àx¦s®æ¿é¤J=IFERROR(IF(COLUMN(A1)=1,"","¡B")&INDEX($D:$D,SMALL(IF(ISNUMBER(0/(FIND($D$10:$D$18,"@"&$A2)-1)),ROW($10:$18)),COLUMN(A1)))&C2,"")
«öctrl+shift+enter
¦ý¨SÅã¥Ü¥X§Æ±æµ²ªG |
|
joyce
|
|
|
|
|
- ©«¤l
- 1391
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1401
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-12-16
|
¥»©«³Ì«á¥Ñ hcm19522 ©ó 2021-11-9 15:53 ½s¿è
¦^´_ 6# leiru
¤w¦¨°Ï°ì°}¦C B2:B7¥ý¤@°_§R B2«·s ¦b¥k©Ô ¤U©Ô
A5 ¦hÓ 3 |
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 2839
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2895
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-12-22
|
¦pªGexcelª©¥»¨S¦³TEXTJOIN¨ç¼Æ, ¥Îvba¼gÓÃþ¦üªº¦Ûq¨ç¼Æ:
Xl0000808.rar (11.15 KB)
¤½¦¡//°}¦C
=TEXTJOIN("¡B",1,IF(1-ISERR(0/(FIND(D$2:D$16,"_"&A2)>1)),D$2:D$16,"")) |
|
|
|
|
|
|
- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-12-5
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Brr, Crr, i&, x%, T$, T1$
Brr = Range([D2], [D65536].End(3))
Crr = Range([A2], [A65536].End(3))
For i = 1 To UBound(Crr)
T = Crr(i, 1)
Crr(i, 1) = ""
For x = 1 To UBound(Brr)
T1 = Trim(Brr(x, 1)): If T1 = "" Then GoTo x01
If InStr(T, T1) Then
Crr(i, 1) = IIf(Crr(i, 1) = "", T1, Crr(i, 1) & "¡B" & T1)
End If
x01: Next
Next
[C2].Resize(UBound(Crr), 1) = Crr
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|