Stop Excel from converting text in cell to scientific notation

Question

In my attempts to remove the spaces from the cell "71646E 10 0" I get "5.58E+104" when I use Ctrl+H. There are about 100 cells for me to fix. I can do them one at a time by editing each cell and deleting the space.

Is there a function that can to this all at once?

Answer 1

You were on the right track, the simple solution is to remove scientific notation from displaying is to highlight the column, select custom format and enter just a zero (0) in the Type.

Answer 2

New Answer

So my original answer of being clever with a Custom Format doesn't actually work.

However it seems you can simply format the cells to Text, then remove the spaces and all Excel will do is complain (with little green triangles on every cell where you do this) that there's a number stored in Text.

Old Answer

My go-to for this is to just slam a Custom Format on the cells I want to have show exactly what is in them.

It's pretty dirty, but it does the trick and can be applied to whole selections at a time.

Answer 3

With data like:

run this short VBA macro:

Sub RemoveSpaces()
    Dim r As Range
    For Each r In Selection
        r.Value = "'" & Replace(r.Value, " ", "")
    Next r
End Sub

to produce:

Answer 4

In attempts to remove the spaces from the cell, you can try this simple equation:

=SUBSTITUTE(B64," ","")

Note, adjust cell references in formula as needed.