收集和自编的自定义函数(21个)!新增"字符快速合并"函数（9月14日新增）

gpf199

[em02]

周宪臣

楼主辛苦了，谢谢你。

好东西先收藏了，有空慢慢看～

嫦娥仙子

楼主，为什么我加载后系统不能运行呢，每次打开EXICE都会弹出提示：找不到工程或库!是什么原因呢？[em06]

nbreeze13

好东西!慢慢学

CJR33527

老兄：能否帮忙编写一个这样的函数：只进行单元格内数字的运算，而要单元格内的忽略非数字。谢谢

zhouxl2000

将将字符串trstr中的frstr替换成tostr

Function subinstr(ByVal trStr As Variant, frStr As String, toStr As String) As Variant '将字符串trstr中frstr替换成tostr Dim iRow As Integer Dim iCol As Integer Dim j As Integer Dim Ar As Variant Dim vfr() As String Dim vto() As String ReDim vfr(1 To Len(frStr)) ReDim vto(1 To Len(frStr)) For j = 1 To Len(frStr) vfr(j) = Mid(frStr, j, 1) If Mid(toStr, j, 1) <> "" Then vto(j) = Mid(toStr, j, 1) Else vto(j) = "" End If Next j If IsArray(trStr) Then Ar = trStr For iRow = LBound(Ar, 1) To UBound(Ar, 1) For iCol = LBound(Ar, 2) To UBound(Ar, 2) For j = 1 To Len(frStr) Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j)) Next j Next iCol Next iRow Else Ar = trStr For j = 1 To Len(frStr) Ar = Application.Substitute(Ar, vfr(j), vto(j)) Next j End If subinstr = Ar End Function

[此贴子已经被作者于2005-9-6 11:37:41编辑过]

zhouxl2000

1、依据分隔符提取第N个字符（8月28日改编）

结构: TQ( 引用单元格,N,分隔符 )

如A1=12-AA-GER-FES-FEE-FEGREG-325D-FEFDS

=TQ(A1,6,"-") 的结果为FEGREG

无法提取最后一个字符串，故对其作了适当的修改，使其能支持最后一个字符串的提取。

鉴于字符串多以空格分离，另将tq()函数分解出来：

一、word(Txt, n)：提取txt中第n个单词，单词间以空格分隔，n=-1，表提取最后一个单词

二、mword(Txt, n, Separator)：依据分隔符Separator提取第n个字符串，n=-1表最后一个字符串

三、wordcount(txt,separator)：依据分隔符Separator计数txt中字符串个数

Function word(Txt, n) As String '提取txt中第n个单词，单词间以空格分隔，n＝-1表最后一个单词 Dim AllElements As Variant Dim last As Integer Txt = Application.Trim(Txt) AllElements = Split(Txt, " ") last = UBound(AllElements) If n <> -1 Then word = AllElements(n - 1) Else word = AllElements(last) End If End Function

Function mword(Txt, n, Separator) As String '依据分隔符提取第n个字符串，n＝-1表最后一个字符串 Dim AllElements As Variant Dim last As Integer If Separator = " " Then Txt = Application.Trim(Txt) Else Txt = Txt End If AllElements = Split(Txt, Separator) last = UBound(AllElements) If n <> -1 Then mword = AllElements(n - 1) Else mword = AllElements(last) End If

End Function

Function wordcount(Txt, Separator) As Integer '依据分隔符计数txt中字符串的个数 Dim AllElements As Variant Txt = Application.Trim(Txt) AllElements = Split(Txt, Separator) wordcount = UBound(AllElements)+1 End Function

另外，自定义的函数只需将其复制到VB编辑器中的模块中即直接可使用，或插入函数－自定义－

[此贴子已经被作者于2005-9-6 11:46:48编辑过]

biton

down下好好学习

guoyf

好，版主辛苦

alicefeng

怎麼沒有權限看啊