Stop Excel from changing cell contents, ever

Question

I work with card numbers, like credit card and ID numbers. We do not do any calculations with card numbers, obviously. They are "text."

I format them as text, I type them like text. I know how that works. Excel doesn't care. 16 digit card numbers get their last digit turned into a zero, changed into scientific notation, stupid stuff that I did not tell Excel to do.

I need to do things like Find/Remove spaces from cells in files downloaded from our currently imperfect web-system. The system sends me files with 16 digit numbers, cells formatted as text, but due to bugs there are spaces at the end. I do Find/Remove all spaces and all my card numbers are transformed into scientific notation and the last digit turned into a 0. THEY ARE TEXT, they are formatted as text, I yelled into the screen that they are text, why does Excel refuse to acknowledge that they are text? (I would rather find a way to stop Excel's action than find a way to tell our programmers to put an apostrophe in every cell)

How do I make it so that Excel just STOPS doing anything that I didn't tell it to do? Or at least stop it from doing anything to numbers it doesn't like. Maybe I can write a macro for whenever it discovers "Uhoh I should change that number to something different!" I'll make it format that cell to text a thousand times instead.

Give me an error when I try calculating with a number larger than 15 digits, make my computer explode violently, that's fine. Just stop changing the numbers.

Is it possible? I have many thousands of numbers that need changing in many different scenarios. I just want to stop it from trying to help. I can't understand why that would be difficult. I have 2007, but answers for other versions would be great as well.

Thank you!

Answer 1

Either

• format the cells as text (Format, Cells, Text)
• prefix the number with a ' (single quote)

Both work equally well for me.

Edit: I tested replacing spaces by nothing on those "text numbers", and indeed, they turn into numbers.

Try Googling for "regex excel". There are ways to use regular expressions in Excel, and that would allow you to quite easily prefix your credit card numbers by a single quote.
Morefunc

Answer 2

This is a "feature" of Excel that cannot be changed or turned off. If you programmers have managed to take the spaces out of the credit card numbers, I'm sure they can handle appending an apostrophe on the front of the string.

Answer 3

Here's a macro that might do what you want

Sub SafeFindAndReplace()

    Dim rCell As Range

    For Each rCell In ActiveSheet.UsedRange.Cells
        If InStr(1, rCell.Text, " ", vbBinaryCompare) > 0 Then
            If rCell.NumberFormat = "@" Then
                rCell.Value2 = CStr(Replace(rCell.Text, " ", ""))
            End If
        End If
    Next rCell

End Sub

Be sure to try it on a copy of your data, not the live stuff, to make sure it works.

Or per the comments, you can just do it on the selected cells. Better if you have really big ranges and you only need it on a smaller subset.

Sub SafeFindAndReplaceSelection()

    Dim rCell As Range

    If TypeName(Selection) = "Range" Then
        For Each rCell In Selection.Cells
            If InStr(1, rCell.Text, " ", vbBinaryCompare) > 0 Then
                If rCell.NumberFormat = "@" Then
                    rCell.Value2 = CStr(Replace(rCell.Text, " ", ""))
                End If
            End If
        Next rCell
    End If

End Sub