Function =Trim()

[Dowitch]

I've been using Trim to delete extra spaces from fields that contain imported
data. Today I tried to use it on data that I'd copied from a webpage and
inserted into a spreadsheet, but it wouldn't take off the blanks after the
numbers. Am I mis-using the function? These were numbers that I'd copied as
a bulk which 'self-segregated' into individual cells. I then re-copied them
changing from colums to rows using Paste-Special. Then I tried to use Trim,
but it had no effect. I needed to remove the spaces so that I could divide
the numbers by 100.

 

[Luke M]

If you're dealing with numbers stored as text (have extra spaces) it might be
better to use the VALUE function to convert the text to a number.

Or, note that if you have " 500 " in A2 formatted as text, you can still
have another cell have
=A2/100
and it will show correct answer (5)

 

[Dowitch]

I didn't know about the VALUE function. I'll use that next time.

I found the problem when I tried to use the Paste Special function to divide
the series. When that didn't work, I tried to go to a separate cell and
create a function (=A2/C2) where C2 = 100, but got an error message. I had
what appeared to be "500 ". Is that handled differently if there are no
leading spaces?

 

[Luke M]

No, leading or nonleading should not have made a difference...
After trying to duplicate your situation, perhaps what you see is not truly
a "space", but some other nonprintable character. In which case, use the
CLEAN function to remove those characters, and then you'll be able to use
either the VALUE or direct math function to fully convert to a working number.

 

[Dowitch]

Looks like I lied. Neither CLEAN nor VALUE solve the problem. Both leave
what appear to be spaces after the number. Doing a Find/Replace for the
spaces does eliminate them. Is that the only way?

 

[T. Valko]

If you don't want to use the time-saving macro...

Select the cells that contain these numbers.
Goto the menu Edit>Replace
Find what:

Hold down the ALT key and using the **numeric keypad** type 0160

Release the ALT key. When you do that you won't see anything in the "Find
what" box but there is a char 160 space in there.

Replace with: nothing, leave this blank
Replace All

That will remove all those char 160 characters then you can use normal
formulas.