当前位置：首页 → 问答吧 → 求帮助如何提取中文字符到同一单元格

求帮助如何提取中文字符到同一单元格

时间：2011-08-01

来源：互联网

本人VBA菜鸟，求高手解释，尽量详细点

附件

提取中文字符到同一单元格.zip(6.9 KB)

2011-8-1 20:38, 下载次数: 10

提取中文字符到同一单元格

作者: llautumn 发布时间: 2011-08-01

自己顶一下

作者: llautumn 发布时间: 2011-08-01

=SUBSTITUTE(TRIM(IF(LEN(A1)=LENB(A1),"",MID(A1,LEN(A1)*2-LENB(A1)+1,20))&" "&IF(LEN(A2)=LENB(A2),"",MID(A2,LEN(A2)*2-LENB(A2)+1,20))&" "&IF(LEN(A3)=LENB(A3),"",MID(A3,LEN(A3)*2-LENB(A3)+1,20))&" "&IF(LEN(A4)=LENB(A4),"",MID(A4,LEN(A4)*2-LENB(A4)+1,20)))," ",",")

附件

提取中文字符到同一单元格.rar(6.9 KB)

2011-8-1 21:00, 下载次数: 4

作者: yangrongguan 发布时间: 2011-08-01

复制内容到剪贴板

代码:

Sub by()
Dim aa$
With CreateObject("VBSCRIPT.REGEXP")
.Global = True
.Pattern = "\w"
For Each a In Range("a1:a" & [a65536].End(xlUp).Row)
a = .Replace(a, ""): If Len(a) > 0 Then a = a & ",": aa = aa & a
Next a
For Each b In Range("b1:b" & [b65536].End(xlUp).Row)
b = .Replace(b, ""): If Len(b) > 0 Then b = b & ",": bb = bb & b
Next b
End With
MsgBox Left(aa, Len(aa) - 1)
MsgBox Left(bb, Len(bb) - 1)
End Sub

作者: 白云2011 发布时间: 2011-08-01

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC(A1:A4),"a"," "),"b"," "),"c"," "),"d"," "),"e"," "))," ",",")
这个短一些

作者: yangrongguan 发布时间: 2011-08-01

复制内容到剪贴板

代码:

Function getChsChar(rng As Range) As String
Dim oRegExp As Object, arr, arrRet()
Dim i As Long
ReDim arrRet(1 To rng.Count)
Set oRegExp = CreateObject("vbscript.regexp")
arr = rng
With oRegExp
      .Global = True
      .Pattern = "[^一-龥]"
      For i = 1 To UBound(arr)
         If .test(arr(i, 1)) Then arrRet(i) = .Replace(arr(i, 1), "")
      Next i
      .Pattern = ",$|^,|(,),*"
      getChsChar = .Replace(Join(arrRet, ","), "$1")
End With
End Function

作者: livewire 发布时间: 2011-08-01

请问这个公式如果变成数组怎么改?

作者: 白云2011 发布时间: 2011-08-01

谢谢各位了

作者: llautumn 发布时间: 2011-08-03