excel - wrap cell in quotes unless value is NULL

Question

I have a table in excel that I'm eventually going to read into a sql database. I have \"@\" as a custom code to wrap the text with quotes. However, I also tried to replace all the blank values with NULL, but those also get quotes wrapped around them.

Is there a custom code I can use that'll wrap every string except NULL in text?

is `\"@\"` a formula or something? I'm not sure I understand how it's being used. Also, when you say you want to `wrap every string ... in text` do you mean surround it with quotes? — Marcucciboy2, Sep 20 '18 at 17:34
it's a custom code for cell formatting. And yes, I mean surround with quotes — JesusMonroe, Sep 20 '18 at 17:35
ah, okay, found what you're currently using [here](https://stackoverflow.com/a/41356775/2727437). I'm looking around [here](https://exceljet.net/custom-number-formats) to see how to whip up an answer for you that still uses number formats. If I can't, though, are you opposed to a vba solution? — Marcucciboy2, Sep 20 '18 at 17:48

score 1 · Accepted Answer · answered Sep 20 '18 at 18:39

After a bunch of research (1, 2, 3, 4, 5), and finding myself impressed at how many features that custom number formats actually has, it appears to me that number formats can't do what you're looking for.

It can't compare the actual value of a cell against a given value (like "NULL"); it can only be used to discern between the type of value stored in a cell, whether it's one of these: POSITIVE; NEGATIVE; ZERO; TEXT.

With that in mind, you could do this pretty easily with a VBA solution. Since I'm guessing a bit at your situation, here are some options.

This first option will not automatically change the number format of your cells, but if you don't mind 'manually' updating the number formats, you could run this macro below to force it to update the number formats.

Option Explicit

Sub NumberFormatting()

    Dim rng As Range
    Set rng = Range("A1:B4")    'adjust range as necessary

    Dim cel As Range
    For Each cel In rng.Cells
        If cel.Value2 = "NULL" Then
            cel.NumberFormat = "@"
        Else
            cel.NumberFormat = "\""@\"""
        End If
    Next cel

End Sub

If you wanted the number formatting to change automatically, you can drop this code into the sheet's module and any time you update a cell within the specified range it will check whether that new value is "NULL" and update the number format when that is the case.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Set rng = Range("A1:B4")    'adjust range as necessary

    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value2 = "NULL" Then
            Target.NumberFormat = "@"
        Else
            Target.NumberFormat = "\""@\"""
        End If
    End If

End Sub

excel - wrap cell in quotes unless value is NULL

1 Answers1