Excel单元格值作为字符串不会作为字符串存储 [英] Excel cell value as string won't store as string
问题描述
我无法在此处获取此代码以获取单元格内容并作为字符串存储。我得到一个双倍:
54.6666666667
而不是 N03:DM:
(示例单元格内容)
I can’t get this code here to grab cell content and store as string. I get a double:
54.6666666667
instead of N03:DM:
(example cell contents).
如果我使用 Cstr(Sheet1.Cells(i,5).Value)
我仍然得到相同的结果
If I use Cstr(Sheet1.Cells(i, 5).Value)
I still get same result.
任何帮助将不胜感激。
Option Explicit
Private Sub GetAddress()
Dim varAdd As String
Dim i As Integer
For i = 2 To 327
If varTag = Sheet1.Cells(i, 2).Value Then
varAdd = Sheet1.Cells(i, 5).Value
varAdd = Left(varAdd, 7)
Sheet3.Cells(incR, 2).Value = varAdd
Exit For
End If
Next i
End Sub
图片截图
推荐答案
使用 Range(A1)。文本
而不是 .Value
Use Range("A1").Text
instead of .Value
发表评论编辑:
为什么?
由于Range对象的 .Text
属性返回在电子表格中可以看到的内容,所以如果单元格显示例如 i100l:25he * _92
then< - Text
将精确地返回单元格中的内容,包括任何格式。
.Value
和 .Value2
属性返回存储在单元格中的内容罩不包括格式。特殊 .Value2
对于日期类型,它将返回十进制表示。
post comment edit:
Why?
Because the .Text
property of Range object returns what is literally visible in the spreadsheet, so if you cell displays for example i100l:25he*_92
then <- Text
will return exactly what it in the cell including any formatting.
The .Value
and .Value2
properties return what's stored in the cell under the hood excluding formatting. Specially .Value2
for date types, it will return the decimal representation.
如果你想深入了解意思和表现,我刚刚发现 这篇文章
,这似乎是一个很好的指导
If you want to dig deeper into the meaning and performance, I just found this article
which seems like a good guide
另一个编辑
你去@Santosh
在( MANUALLY )中键入 DEFAULT (col A)到其他列
不要格式化列A全部
格式列B为文本
格式列C为日期[dd / mm / yyyy]
将列D格式化为百分比
现在,
将此代码粘贴到模块中
another edit
Here you go @Santosh
type in (MANUALLY) the values from the DEFAULT (col A) to other columns
Do not format column A at all
Format column B as Text
Format column C as Date[dd/mm/yyyy]
Format column D as Percentage
now,
paste this code in a module
Sub main()
Dim ws As Worksheet, i&, j&
Set ws = Sheets(1)
For i = 3 To 7
For j = 1 To 4
Debug.Print _
"row " & i & vbTab & vbTab & _
Cells(i, j).Text & vbTab & _
Cells(i, j).Value & vbTab & _
Cells(i, j).Value2
Next j
Next i
End Sub
和 分析
输出!它真的很容易,没有更多的我可以做的帮助:)
and Analyse
the output! Its really easy and there isn't much more i can do to help :)
.TEXT .VALUE .VALUE2
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 4 1 1 1
row 4 1 1 1
row 4 01/01/1900 31/12/1899 1
row 4 1.00% 0.01 0.01
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 6 63 63 63
row 6 =7*9 =7*9 =7*9
row 6 03/03/1900 03/03/1900 63
row 6 6300.00% 63 63
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013 29/05/2013 29/05/2013
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013% 29/05/2013% 29/05/2013%
这篇关于Excel单元格值作为字符串不会作为字符串存储的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!