0

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

user3772305
  • 29
  • 4
  • The only thing I could think to do is to use the =value() which will turn a zero length string in VALUE#! which is then considered not text. Seems like there has to be a better way around this though? – user3772305 Sep 14 '14 at 20:55
  • When I am importing Excel to Access, I've avoided this by first reducing the formulas to values then reducing the zero length string values to true blank cells with Text-to-Columns, Fixed Width, Finish on each column that requires it. You'll be left with truly blank cells that will come into Access' currency field as Nulls. –  Sep 15 '14 at 11:55
  • Any other suggestions? I would like to not use any vba. Is there some type of formula I could use in access to convert the ZLS to an actual blank? – user3772305 Sep 17 '14 at 01:37
  • There is none that I am aware of. Local in-depth discussion here: [Return empty cell from formula in Excel](http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel). Excel does not have the equivalent of NULL that can be returned to a cell by a formula to make it appear truly blank. –  Sep 17 '14 at 01:56

1 Answers1

0

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

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.