ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó±Æ°£­^¤å¦r¥À¤Î"-"¦r²Å¨ú¥þ³¡¼Æ¦r?

[µo°Ý] ¦p¦ó±Æ°£­^¤å¦r¥À¤Î"-"¦r²Å¨ú¥þ³¡¼Æ¦r?

0708.jpg
¦p¦ó±Æ°£­^¤å¦r¥À¤Î"-"¦r²Å¨ú¥þ³¡¼Æ¦r?
°w¹ïÀx¦s®æ¤º§t¦³­^¼Æ¤Î¨ä¥L¦r²Å®É
§Q¥Î¤½¦¡±N¨ä²Î¾ã¬°¼Æ¦r«¬ºA
°ÝÃD¬°¨Ò  ­YB4Àx¦s®æ¬° 085-8
                      B5Àx¦s®æ¬° 5-5-0-5
¤W­z¤½¦¡¸Ó¦p¦ó­×¥¿¤~¯à§¹¾ã§e²{¬° 0858¤Î5505 ?

0708.rar (7.12 KB)

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-7-8 15:52 ½s¿è

°}¦C¤½¦¡(¤TÁä):
=MID(LOOKUP(,-(1&SUBSTITUTE(MID(A1,MATCH(,MID(A1&1,ROW($1:$99),1)*0,),ROW($1:$15)),"-",))),3,15)

=MID(LOOKUP(,-(1&SUBSTITUTE(MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$15)),"-",))),3,15)

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 2# ­ã´£³¡ªL

·PÁ­㴣³¡ªLª©¥D´£¨Ñ¸Ñ´bªº¤è¦¡
´£¨Ñ¥H¤W¤GºØ¸ÑÃD²`Àò¨}¦h   ^^

TOP

¦^´_ 3# hcm19522
·PÁÂ
ÀH·NºÛ "EXCEL°g"  blog  ©Î http://blog.xuite.net/hcm19522/twblog
´£¨ú¼Æ¦r ±qA¦C (7123)¤§½d¨Ò¸Ñµª°Ñ¦Ò...^^

TOP

¦^´_ 1# cypd


ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,¥H¤U¬OVBAªº¤è¦¡,½Ð«e½ú°Ñ¦Ò
½Ð±Ð«e½ú­Ì:
¥¿«hªºµ²ªG¥i¥H¥ÎJoinª½±µ³s±µ°_¨Ó¶Ü?

°j°é³s±µ°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Fn, Rg, Brr, Crr, i&, j&, Sh
Set Sh = ActiveSheet
Brr = Range(Sh.[A1], Sh.[A65536].End(3))
Set Rg = CreateObject("VBScript.RegExp")
Rg.Pattern = "\d+"
Rg.Global = True
ReDim Crr(1 To UBound(Brr), 0) As String
For i = 1 To UBound(Brr)
   Set Fn = Rg.Execute(Brr(i, 1))
   If Fn.Count > 0 Then
      For j = 0 To Fn.Count - 1 '½Ð±Ð¦U¦ì«e½ú,¥i¥H¥ÎJoin¨ú¥N³o°j°é¶Ü?
         Crr(i, 0) = Crr(i, 0) & Fn(j)
      Next
   End If
Next
Sh.[D1].Resize(UBound(Brr), 1) = Crr
End Sub

'¥H¤U¬O´ú¸Õ¤@ºû°}¦CJoin
Sub ¤@ºû°}¦CJoin()
Dim T
T = [{1,2,3,4,5,6}]
MsgBox T(1)
MsgBox Join(T)
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# Andy2483


    ¦^´_¦Û¤vªº¾q¶w
¤£¦AªÈµ²Join
Âà©À:¤Ï¦V«ä¦Ò

¥H¥¿«hµ²ªG¸m´«:


Option Explicit
Sub TEST() '¤µ¤Ñ¾Ç²ß¨ì ¥H¥¿«hµ²ªG¸m´«
Dim i&, Brr, Sh
'¡ô«Å§iÅܼÆ:i¬Oªø¾ã¼ÆÅܼÆ,¨ä¥L¬O³q¥Î«¬ÅܼÆ
Set Sh = ActiveSheet
'¡ô¥OSh³o³q¥Î«¬ÅܼƬO²{¥Î¤u§@ªí
Brr = Range(Sh.[A1], Sh.[A65536].End(3))
'¡ô¥OBrr¬O ¤Gºû°}¦C!¥H[A1]¨ìAÄ榳¤º®e³Ì«á¤@­ÓÀx¦s®æ,³o½d³òÀx¦s®æ­È­Ë¤J
With CreateObject("VBSCRIPT.REGEXP")
'¡ô¥H¤U¬O¦³Ãö©ó¥¿«hªºµ{§Ç
   For i = 1 To UBound(Brr)
   '¡ô³]¶¶°j°é!i±q1¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
      .Global = True
      '¡ô³]©w¥þ§½¥i¥Î
      .Pattern = "\D+"
      '¡ô¤Ç°t¼Ò¦¡¬°«D¼Æ¦r
      Brr(i, 1) = "'" & .Replace(Brr(i, 1), "")
      '¡ô¥Oi°j°é¦C²Ä1ÄæBrr°}¦C­È¬O "'"³æ¤Þ¸¹³s±µ Brr°}¦C­È¦Ü´«±¼«D¼Æ¦rªº¥¿«h
   Next
End With
Sh.[E1].Resize(UBound(Brr), 1) = Brr
'¡ô¥O[E1]¦V¤UÂX®i Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ¦C,¦V¥k¤£ÂX®iªº½d³ò¥HBrr°}¦C­È­Ë¤J
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 7# Andy2483

«D±`·PÁ±z¥Î¤ß¦^ÂÐ:
¦^´_ 6# Andy2483

    ¦^´_¦Û¤vªº¾q¶w
¤£¦AªÈµ²Join
Âà©À:¤Ï¦V«ä¦Ò

¥H¥¿«hµ²ªG¸m´«:
¦¹°ÝÃD¨t¥H±b¸¹¥X²{²£¥Í...
­Y¥H  A8 Àx¦s®æ¤º®e...SWF´ú¸Õ-0258(¦³®É·|¥X²{¤¤¤å¦r)
¸Óµ{¦¡½X­n¦p¦ó­×¥¿¡H  ÁÂÁ±z

TOP

¥u­n¼Æ¦r
.Pattern = "[^0-9]"

TOP

¦^´_ 7# Andy2483
«D±`·PÁ±z¥Î¤ß¦^ÂÐ:
¦^´_ 7# Andy2483

©Ò­z°ÝÃD§t¤¤­^¤å¦rµ¥
¸g¹ê´ú©Ò±oÅã¥Üµ²ªG¥¿½T³á  ^^

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD