2

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?

  • If simply you are trying to remove the White spaces, then applying Scientific notation is never a good idea , and could you write the original value since 71646E 10 0 is not just a number !! – Rajesh S Jul 11 '18 at 8:47
  • I guess they are non-printable characters, you may try this =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))) – Rajesh S Jul 11 '18 at 8:52
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.

Customer Number format

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

2

With data like:

enter image description here

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:

enter image description here

1

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

=SUBSTITUTE(B64," ","")

enter image description here

Note, adjust cell references in formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.