excel VBA 将文本数值转换为数字格式&数字格式转换为文字数值格式(转http://hi.baidu.com/xianyunyehe/item/7874e940fe7ce797833ae100)

Coordinator
Nov 20, 2012 at 12:14 PM

前几天因工作需要编写VBA整理文本资料,其中含有数值和时间。将VBA运行后发现整理后的单元格数值和时间都问文本型数值的储存方式,在网上找了很多没有相关的VBA代码,只能手动转换后在进行后续的操作。浪费了大量时间。而且时间的显示格式是一串数字“3.79513888888889E-02”。今天又在网上搜了一下发现有个操作录制成宏可以有效的解决这一问题。按照如下提示操作不管是连续的还是不连续的文本型数值都可以转换,而且表格CELL无变化。

选择A列对A列进行分列操作

将分列条件全部去掉后完成操作

看看效果


 

代码如下:实际操作为选中A列,分列。关闭所有条件。每次1列。如果为多列请改发光部分

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

实际事例:

以下为原始表,CELLS左上标有绿色标记说明此处为文本型数字。如果不加以转换在后期的排序和函数如VLOOKUP操作时会出错。所以要将其转换为数值型数字。






Sub 宏1()

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

转换后的样子

用以上方法虽然能够实现VBA来转换但是由于表格的列数是固定的。比如我的代码是4列超过5列就不能转换了我们可以改造一下我们的代码让它能够适应任意列而且每列不连续的需要转换的代码

经过改造后代码如下:

Sub zhuan()

Dim i, k As Integer
i = Range("IV2").End(xlToLeft).Column

Application.ScreenUpdating= False

For k = 1 To i
Columns(k).Select
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Next

End Sub

 

给excel添加个按钮引用一下,完事。

另附将D列转换为时间格式的代码

Columns("D:D").Select
Selection.NumberFormatLocal = "h:mm:ss;@"

最后来一张最终效果图


下面为转发文章我试了下还行

Excel 数字转化成以文本形式存储的数字

主题:Excel 如何以文本形式存储的数字|Excel 数字转化成以文本形式存储的数字

看到这个标题有点惊异吧,一般情况下都是问怎样把“以文本形式存储的数字”转化成“一般数字”,这下怎么倒过来了,是的,不过举一反三,这样遇到问题了才会 游刃有余呀。往下看吧。

最近使用一个OA系统,其中的数据导入和导出比较方便,但是在进行数字方面的导入时出了些问题,表征如下:

1、 导入时提示一切正常

2、数字部分在“查看数据源时”就没有显示,而事实是有的

3、采用系统建议的复制新文件法无效

详细分析:

1、数字部分当以'(单引号)+数字的形式输入时,形成以文本形式存储的数字时系统是可以接受的

2、在1的基础上得出以下结论,由于系统数据采用数据库结构,故存在数据类型问题,而用excel 表本身的数据类型和此OA系统用的数据库类型(估计是类似dBaseIV的数据库)是不匹配的,所以只能采用文本方式导入

3、到此,问题明了,解决方法也浮出水面,只要将所有的数字装换成以文本形式存储的数字即可。

解决方法:

其实这个才是本次问题的重点, 平时输入时只要用以'(单引号)+数字就可以形成以文本形式存储的数字,而对于以文本形式存储的数字到普通数字的转换是很简单的,只要大片选中再单击 “!”符号中的“转换为数字即可”。但是,现在我们要解决的问题是将数字转换为“以文本形式存储的数字”,一个一个加单引号是不现实的,而用文本组合符号 的方式来加一个单引号也是不行(Failed),其他方式诸如单元格格式、转换文件类型等等,都无效(试验得好辛苦,凡是能想到的都用上了)。最后,经过无数次的失败总结出以下方法:(试验结果显示简单且易行)

前提:批量、转换、数字到以文本形式存储的数字

1、正常输入所 有数字数据

2、在新的一列中用函数TEXT转换数字为文本,如=TEXT(A1,0)

3、复制、选择性粘贴、“值”,取 消共识,保留公式结果,这是令人兴奋的小绿箭头已经出现了

4、替换原来的数字,OK
=====================================================================
我的补充:
1、正常输入所有数字数据(比如在D1:D10)
2、 在新的一列(比如是G列)中用函数TEXT转换数字为文本, 如 =TEXT(D1,0) ,将其他D1:D10进行公式复制
3、复制新得到的(G1:G10)、再在(G1:G10)区域右击【选择性粘贴】、数值,这时令人兴奋的 小绿箭头已经出现了
4、再次复制新得到的(G1:G10)替换原来的D1:D10中的数字,OK