零长度字符串Microsoft Access

问题描述

在excel中，我有一个文本字符串，其中包含数字和文本，我用公式删除数字。但是，有时候，文本字符串可能是空白的，所以如果发生这种情况，我使用返回一个空白而不是0.当我将这个excel表链接到访问数据库时，它不会让我格式化列作为货币，因为它作为文本拾取，同时将数字作为数字删除。任何解决方案如何解决这个问题？除了使单元格完全空白而不是零长度字符串之外，还有另一种方法吗？

In excel, I have a text string, which contains numbers and text, that I strip the numbers out of with a formula. However, sometimes it is possible that the text string will be blank to begin with so if this happens I use "" to return a blank instead of a 0. When I link this excel sheet to an access database it will not let me format this column as currency because it is picking up the "" as text along with the stripped out numbers as numbers. Any solutions on how to fix this? Is there another way besides "" to make a cell completely blank and not a zero length string?

这正是我遇到的问题：

This is along the lines of the problem I am having:http://support.microsoft.com/kb/162539

推荐答案

这是一个快速例程，可将公式减少到其值，并将零长度字符串剥离成真正的空白单元格。

Here is a quick routine that reduces formulas to their values and strips zero length strings to truly blank cells.

Sub strip_zero_length_string()
    Dim c As Long
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        .Cells = .Cells.Value
        For c = 1 To .Columns.Count
            .Columns(c).TextToColumns Destination:=.Cells(1, c), _
              DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        Next c
    End With
End Sub

我正在使用.CurrentRegion，所以数据块中没有完全空白的行或列，但这是通常的在准备导出到数据库时的情况。

I'm using .CurrentRegion so there can be no completely blank rows or columns within your data block but that is usually the case when preparing to export to a database.

这篇关于零长度字符串Microsoft Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！