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

每次打开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]日期格式、文本格式快速转换

使用Excel的VBA下载文件

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

附件如下:xls

这是代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sub
downloads()
    
Dim
i
As
Integer
    
Dim
Path
As
String
    
Dim
str
As
String
    
Application.ScreenUpdating =
False
    
Application.EnableEvents =
False
    
On
Error
Resume
Next
    
MkDir ThisWorkbook.Path &amp;
"Downloads"

       

'图片文件的存放目录
    
Path = ThisWorkbook.Path &amp; "Downloads"
    
For
i = 2
To
Sheet1.Range(
"a65534"
).
End
(xlUp).Row
    
'A列中存放着图片的文件路径 <a href="http://www.xxx.net/photo/xxxx.gif">http://www.xxx.net/photo/xxxx.gif</a>
    
str = Sheet1.Range(
"a"
&amp; i)
    
Set
ie = CreateObject(
"Msxml2.XMLHTTP"
)
        
ie.Open
"GET"
, str,
False
        
ie.Send
        
'str = ie.ResponseText
        
'等待网页处理完成再运行下面的代码
        
Do
Until
ie.ReadyState = 4
            
DoEvents
        
Loop
       
With
CreateObject(
"ADODB.Stream"
)
            
.Type = 1
            
.Open
            
.write ie.Responsebody
            
'B列存放着新的文件名
            
.savetofile Path &amp; Sheet1.Range(
"b"
&amp; i) &amp; Right(str, 4), 2
            
.Close
        
End
With
    
Next
    
Application.ScreenUpdating =
True
    
Application.EnableEvents =
True
End
Sub