excel如何把多sheet合并到一个sheet表里的宏命令

时间：2024-03-01 13:32:25

========================================另外一个合并方式
Sub 合并()
    Dim i As Integer
    For i = 1 To Worksheets.Count Step 1
        Worksheets(i).Range("1:" & Worksheets(i).[a65536].End(3).Row).Copy [a65536].End(3).Offset(1)
    Next
End Sub

=========================================已经成功的宏方法**********
Sub 合并方法2()
    Dim i As Integer
    For i = 1 To Worksheets.Count Step 1
        Worksheets(i).Range("1:" & Worksheets(i).[a65536].End(3).Row).Copy [a65536].End(3).Offset(1)
    Next
End Sub

==============================================================

宏替换代码：

Sub 删所有()
    Cells.Replace What:="座", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="号", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="楼", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="幢", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="栋", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="室", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="null", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


End Sub


 

---------------


Sub 删楼字()
    Cells.Replace What:="楼", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Sub 删号字()
    Cells.Replace What:="号", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Sub 删幢字()
    Cells.Replace What:="幢", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Sub 删空格字()
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Sub 删室()
    Cells.Replace What:="室", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Sub 删xx小区()
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
=============================================================

Excel表格 如何使同在一个单元格内的文字与数字分离

文字在左，数字在右。

 =LOOKUP(9E+307,--RIGHT(E89,ROW($1:$99)))

如上图：会将右侧的数字都取出来。

文字在右，数字在左。

=LOOKUP(9E+307,--LEFT(E89,ROW($1:$99)))

 

-----------------------------------------------

将excel中汉字和数字分开：

比如：
A1：张三12354684
A2：李四/125687324
A3：584388734/张三
先把所有的"/"“，|、 ”空格等都替换掉（使用查找与替换功能）
然后B1={=MID(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>255,),LENB(A1)-LEN(A1))}
C1=SUBSTITUTE(A1,B1,"")

注意B1中的公式为数组公式，输入结束后按Ctrl+Shift+Enter结束。
-------------------------------------------------------------------------------

相关文章