excel中提取连续8位数字

提取A1中8个连续数字:
=IFERROR(0+MID(A1,MATCH(10,MMULT(ABS(ISNUMBER(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-7)),10),{1,2,3,4,5,6,7,8,9,10},1))-{1,0,0,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1;1;1}),0),8),””)

PPT 批量更改字体、颜色

Sub OED01()
Dim oShape As Shape
Dim oSlide As Slide
Dim oTxtRange As TextRange
On Error Resume Next
For Each oSlide In ActivePresentation.Slides
For Each oShape In oSlide.Shapes
Set oTxtRange = oShape.TextFrame.TextRange
If Not IsNull(oTxtRange) Then
With oTxtRange.Font
.Name = “微软雅黑” ‘更改为需要的字体
.Color.RGB = RGB(Red:=0, Green:=0, Blue:=0) ‘改成想要的文字颜色,用RGB参数表示,黑色全为0,白色全为255
End With
End If
Next
Next
End Sub

每次打开excel,所有数据都变成日期格式

一、解决办法:需要删除自定义格式中的格式才可以彻底解决问题。

二、具体步骤如下

1、打开Excel表 点击右键,选择“设置单元格格式”

2、点击“数字”选项卡,在选择“自定义”,将其中默认选择的自定义格式删除

3、返回Excel,数字就变回原样了,而且保存之后再打开也不会再变成日期格式。

解决Excel自动化(Automation)错误发生意外遇到问题(批处理)

今天遇到的问题,打开任意Excel文档,均出现“Microsoft Office Excel 遇到问题需要关闭。我们对此引起的不便表示抱歉。您正在处理的信息有可能丢失。Microsoft Office Excel 可以尝试为您恢复”。

Microsoft Office Excel 遇到问题需要关闭

然后出现“Microsoft Visual Basic 自动化(Automation)错误 发生意外。”的对话框,如下图所示:

自动化(Automation)错误 发生意外

关于Microsoft Office Excel遇到问题需要关闭的信息倒有点类似于之前遇到的由于Normal.dot损坏导致Word打开出现的对话框,于是顺着上次的思路进行手动的修复。

建议大家先阅读我的《解决Word遇到问题需要关闭和“安全模式”启动问题》那篇文章,部分准备步骤与那篇文章描述一致,我就不累述了,一是关闭所有Excel文档,打开“任务管理器”,结束残余的Excel.exe进程;二是打开隐藏文件开关;三是开始运行输入

<span class="pun">%</span><span class="pln">APPDATA</span><span class="pun">%</span><span class="pln">\Microsoft\Excel</span>

以便于打开配置文件夹,分别删除

<span class="pun">%</span><span class="pln">APPDATA</span><span class="pun">%</span><span class="pln">\Microsoft\Excel</span>

<span class="pun">%</span><span class="pln">APPDATA</span><span class="pun">%</span><span class="pln">\Microsoft\Excel\XLSTART</span>

下所有文件,这一步我找到了问题的所在,在XLSTART目录下存在一个带毒的k4.xls文档,这个目录下的Excel文档所包含的程序将会在打开Excel时自动加载,这样k4.xls中所包含的恶意代码就可以执行并感染正常Excel了,所以我们立即删除它;四是找到Office的安装路径,比如我的Office 2003装在D盘Program Files,那么路径就是

<span class="pln">D</span><span class="pun">:</span><span class="pln">\Program </span><span class="typ">Files</span><span class="pln">\Microsoft </span><span class="typ">Office</span><span class="pln">\Office11</span>

,这里Office11对应着Office 2003,同样的Office12对应Office 2007,Office 14对应着Office 2010,好了,找到

<span class="pln">XLSTART</span>

文件夹,删除这个文件夹内的所有文件。

通过以上步骤后,及时升级更新你的杀毒软件,全盘查杀病毒,完成后Excel就可以正常打开了。

当然你也可以尝试我编写的批处理来快速解决这个问题,将下面代码另存为批处理文件(*.bat),双击运行即可修复Excel,如果是Vista、Win7或者更高版本请右击以管理员身份运行,运行前请保存你的所有Excel工作簿并且关闭Excel程序,因为本批处理会强制结束未关闭的Excel运行以保证清理效果:


<span class="pln">REM
REM COPYRIGHT   </span><span class="pun">:</span> <span class="typ">Copyright</span> <span class="pun">(</span><span class="pln">c</span><span class="pun">)</span> <span class="lit">2013</span> <span class="typ">WangYe</span><span class="pun">.</span> <span class="typ">All</span><span class="pln"> rights reserved</span><span class="pun">.</span><span class="pln">
REM
REM DESCRIPTION </span><span class="pun">:</span> <span class="typ">Clear</span><span class="pln"> ALL FILES </span><span class="kwd">in</span> <span class="typ">Excel</span><span class="pln"> XLSTART directory</span><span class="pun">.</span><span class="pln">
REM AUTHOR      </span><span class="pun">:</span><span class="pln"> WANGYE
REM WEBSITE     </span><span class="pun">:</span><span class="pln"> http</span><span class="pun">:</span><span class="com">//wangye.org</span><span class="pln">
REM
REM </span><span class="typ">Compatible</span> <span class="kwd">with</span> <span class="typ">Office</span> <span class="lit">2003</span><span class="pun">/</span><span class="lit">2007</span><span class="pun">/</span><span class="lit">2010</span><span class="pln">
REM
REM </span><span class="typ">Last</span> <span class="typ">Update</span> <span class="pun">:</span> <span class="lit">2013</span><span class="pun">/</span><span class="lit">1</span><span class="pun">/</span><span class="lit">10</span><span class="pln">
REM
</span><span class="lit">@ECHO</span><span class="pln"> OFF
 
</span><span class="pun">:</span><span class="kwd">BEGIN</span><span class="pln">
CLS
ECHO </span><span class="typ">Copyright</span> <span class="pun">(</span><span class="pln">c</span><span class="pun">)</span> <span class="lit">2013</span> <span class="typ">WangYe</span><span class="pun">.</span> <span class="typ">All</span><span class="pln"> rights reserved</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">For</span><span class="pln"> more information please visit http</span><span class="pun">:</span><span class="com">//wangye.org</span><span class="pln">
ECHO</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">Please</span><span class="pln"> wait</span><span class="pun">...</span><span class="pln">
 
TASKKILL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">IM EXCEL</span><span class="pun">.</span><span class="pln">EXE</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">&gt;</span><span class="pln">NUL
 
SETLOCAL </span><span class="typ">EnableDelayedExpansion</span><span class="pln">
 
FOR </span><span class="pun">/</span><span class="pln">L </span><span class="pun">%%</span><span class="pln">i IN </span><span class="pun">(</span><span class="lit">14</span><span class="pun">,-</span><span class="lit">1</span><span class="pun">,</span><span class="lit">11</span><span class="pun">)</span><span class="pln"> DO </span><span class="pun">(</span>
    <span class="kwd">if</span> <span class="pun">%%</span><span class="pln">i NEQ </span><span class="lit">13</span> <span class="pun">(</span><span class="pln">
        SET KEY_NAME</span><span class="pun">=</span><span class="str">"HKLM\SOFTWARE\Microsoft\office\%%i.0\Excel\InstallRoot"</span><span class="pln">
        FOR </span><span class="pun">/</span><span class="pln">F </span><span class="str">"tokens=2*"</span> <span class="pun">%%</span><span class="pln">A IN </span><span class="pun">(</span><span class="str">'REG QUERY !KEY_NAME! /v "Path" 2^&gt;NUL ^| FINDSTR "REG_SZ"'</span><span class="pun">)</span><span class="pln"> DO </span><span class="pun">(</span><span class="pln">
            SET </span><span class="typ">ExcelInstallDir</span><span class="pun">=%%</span><span class="pln">B
            CALL </span><span class="pun">:</span><span class="pln">EMPTY_DIR </span><span class="str">"!ExcelInstallDir!\XLSTART"</span><span class="pun">&gt;</span><span class="pln">NUL
        </span><span class="pun">)</span>
    <span class="pun">)</span>
<span class="pun">)</span><span class="pln">
 
SET EXCELDIR</span><span class="pun">=</span><span class="pln">\Microsoft\Excel
CALL </span><span class="pun">:</span><span class="pln">EMPTY_DIR </span><span class="str">"%APPDATA%%EXCELDIR%\XLSTART"</span><span class="pun">&gt;</span><span class="pln">NUL
DEL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">A </span><span class="pun">/</span><span class="pln">Q </span><span class="str">"%APPDATA%%EXCELDIR%"</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">&gt;</span><span class="pln">NUL
 
GOTO </span><span class="pun">:</span><span class="pln">SUCCEEDED
 
</span><span class="pun">:</span><span class="pln">EMPTY_DIR
ECHO </span><span class="pun">%</span><span class="lit">1</span><span class="pln">
IF EXIST </span><span class="pun">%</span><span class="lit">1</span> <span class="pun">(</span><span class="pln">
  DEL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">A </span><span class="pun">/</span><span class="pln">Q </span><span class="pun">%</span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">&gt;</span><span class="pln">NUL
  RMDIR </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">Q </span><span class="pun">%</span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">&gt;</span><span class="pln">NUL
</span><span class="pun">)</span><span class="pln">
MKDIR </span><span class="pun">%</span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">&gt;</span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">&gt;</span><span class="pln">NUL
GOTO </span><span class="pun">:</span><span class="pln">EOF
 
</span><span class="pun">:</span><span class="pln">SUCCEEDED
ECHO</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">Microsoft</span> <span class="typ">Office</span> <span class="typ">Excel</span> <span class="typ">Recovery</span><span class="pln"> complete</span><span class="pun">:-)</span><span class="pln">
ECHO</span><span class="pun">.</span><span class="pln">
PAUSE
 
</span><span class="pun">:</span><span class="pln">EOF
EXIT</span>

这个批处理主要用于清理Office 2003/2007/2010版本的Excel的XLSTART文件夹内的所有文件,请谨慎使用,由于能力有限本人不确保上述代码没有Bug,如果因此造成的后果本人不承担责任。使用前请安全关闭所有Excel工作簿并及时保存未存档的工作,为保证清理效果,本批处理会强制关闭所有Excel程序。

双击bat后出现Microsoft Office Excel Recovery complete:-)则表明修复完成。

最后不知道怎么保存上述代码的朋友可以直接下载现成的清理XLSTART文件夹修复Excel (135),解压后双击运行即可。

学会Excel:[4]日期格式、文本格式快速转换

Excel表中日期格式其实是数值,有时候需要原样转成文本,有时候也要将文本转成日期。

我发现了一个方法,估计是最快的了。不需要用那一堆year() month()之类的函数。

工具/原料

  • Excel 2007

方法/步骤

  1. 1

    快速将日期格式转化为文本格式

    学会Excel:[4]日期格式、文本格式快速转换
  2. 全选——右键——设置单元格格式,

    可见,为日期格式。

    如果在这里选择文本呢?

    学会Excel:[4]日期格式、文本格式快速转换
  3. 变成了数字,如果选择常规或者数值,都是数字。

    我试很多办法,什么year函数,month函数,等等,麻烦要死。

    终于我发现了一个很简便的办法。

    学会Excel:[4]日期格式、文本格式快速转换
  4. 倒回来,全选这个区域

    学会Excel:[4]日期格式、文本格式快速转换
  5. 点击  数据——分列

    学会Excel:[4]日期格式、文本格式快速转换
  6. 弹出一个框,直接点下一步

    学会Excel:[4]日期格式、文本格式快速转换
  7. 再点击下一步

    学会Excel:[4]日期格式、文本格式快速转换
  8. 选择文本。

    点击完成

    学会Excel:[4]日期格式、文本格式快速转换
  9. 日期已经变成了如假包换的文本格式。

    学会Excel:[4]日期格式、文本格式快速转换
  10. 转回去的操作。也就是将文本格式转成日期格式。

    同样点击分列,到第3步时,选择日期

    点完成

    学会Excel:[4]日期格式、文本格式快速转换
  11. 又变成日期格式了。

    学会Excel:[4]日期格式、文本格式快速转换

word批量删除链接的方法

有时从网上下载文章,文章中含有多个链接,很烦,用这个很不错,需要的可以去试试!

1.CTRL+A

2.CTRL+SHIFT+F9

 

 

特别好玩的还有:

CTRL+F2,可以在打印预览和正常编辑状态切换.

CTRL+F5,可以将全屏的WORD窗口变成缩小的窗口。

CTRL+F6,可以在各个打开的WORD之间切换。

CTRL+F7,可以在不使用鼠标的情况下仅仅使用键盘方向键移动未全屏的WORD窗口。

CTRL+F8,可以在不使用鼠标的情况下仅仅使用键盘方向键改变未全屏的WORD窗口的大小。

CTRL+F9就不用说了,用的比较多,插入一个域代码。

CTRL+F10和CTRL+F5是相反的,可以将未全屏WORD窗口在全屏和未全屏之间来回切换。