分类： Excel

提取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),””)

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

二、具体步骤如下

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

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

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

=MID(G2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&"0123456789",1)),1)

提取G2单元格中第一个数字。

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

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

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

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

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

和

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

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

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

通过以上步骤后，及时升级更新你的杀毒软件，全盘查杀病毒，完成后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)，解压后双击运行即可。

常用的两个方法：

=DATEDIF(A1,B1,”y”)&”年”&DATEDIF(A1,B1,”ym”)&”个月”

=TEXT(B1-A1,”Y年M月D天H小时M分钟”)

将数字复制到文本文件，再从文本文件中复制，在EXCEL中选择性粘贴文本。搞定。

选择整列，数据-分列-下一步-下一步，选择你想要的格式，完成

其实就是转换格式，这个思路不错，记之。

今天发现了个用EXCEL下载文件的实例，看起来很不错，收藏一下。

附件如下：xls