Excel questions - deleting trailing spaces

[NewJeffCT]

I have a column of several hundred numbers. Some are two digit, some three, some four. However, they all seem to have trailing spaces after the numbers, so I can't add, subtract, divide, etc by the numbers and they all have trailing spaces.

I've tried (TRIM) and (CLEAN) functions, but neither worked. Normally, I would do (LEFT, 3) if every number was 3 digits, but I can't do that with numbers that are 2, 3 or 4 digits.

I also tried VALUE(C1) as well, and it didn't work.

A guide online mentioned using TRIM, SUBSTITUTE and CHAR, but that didn't work, either.

I've done similar a thousand times before over the years, but it's just not working this time. I think I'm using Excel 2007.

Thanks

[Xyzse]

1. Let me get this straight.

You have a column which are only numbers but have trailing spaces that you don't know what they are.
My suggestion, if you have data you want to preserve to its right, copy that column and put it at the right end.
Then, depending on which Excel you have, it is in the Data Tab. It is called "Text to Columns".

With that column highlighted, hit "Text to Columns" which is a three step process.
First step is to decide if you want it delimited or fixed width. Pick Delimited.
Second step, use whatever you think needs to be delimited, usually it is "space".
If the extra spaces are on the right of the numbers, you should be fine pretty quick. Otherwise, it would put all the numbers in different columns which you would have to manually place back to one.

Another possible solution is to copy that whole column, paste it on another column blank column, using the right click function and say "Paste Special" = Values Only. Then it will probably post it as the numbers without the space.

Good luck!

[NewJeffCT]

3. already tried both

neither seems to work.

[Xyzse]

5. Damn... I almost want a copy of that excel sheet just to see this marvel.

I can give you an email address, for me to play with it.

[NewJeffCT]

7. you can send me via PM

I've converted text to numbers and vice versa a thousand times before. Not sure why it's not working this time.

[Xyzse]

8. Done

Quite curious about this.

Oh! I edited it, and it is done. Check out if it is ok.

There is a hidden character at the end which I think might be a carriage return. It is now gone.

[NewJeffCT]

11. thanks

I'll check it out. how did you delete the hidden character(s)?

[Xyzse]

12. De nada.

Oh, I used that "Delimited" function I was talking about, but I went to one of those cells, copied the extra space, so that I get whatever hidden character that is, and placed it in "Other".

It got kicked out of there. It was pretty awesome.

[NewJeffCT]

13. did the spreadsheet

get sent back to you? I haven't received yet?

[Xyzse]

14. Check junkmail?

My name is weird, so it could probably have gone there.

Sent it with another email too.

[NewJeffCT]

15. ok

just got it.

Thanks again!

[Xyzse]

16. No prob.

Hope it works.
It seems to be working for me.

[NewJeffCT]

18. Thanks

your sheet is working for me. However, how do I recreate it.

I tried text to columns>delimited>space but nothing happened?

[Xyzse]

20. Use that...

However, go to one of the cells and go to the formula bar, copy the blank space there which is the hidden character.
Then put it in delimited "Other", and paste the copied hidden character there.

That should do it.

[BlueStreak]

2. The cells are stored as text, not numbers

If you want to use those contents in a calculation, you can convert from text to numbers by enclosing the cell reference with the VALUE function.

http://spreadsheets.about.com/od/excelfunctions/qt/81206_value.htm

In LibreOffice, there is a plug-in that will convert all of those text cells to be real number (or date) cells so you can operate on them normally. I don't know if there isa plug-in like that for Excel.

[NewJeffCT]

4. they're numbers

already tried Value(c20) as well. It gave me #VALUE!

[BlueStreak]

6. You might try this

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=70

Or perhaps =VALUE(TRIM(....cell ref...))

[DrDan]

17. highlight the column

then do an "edit - replace" putting a space in the first box and nothing in the second

the hit the "replace all"

this will get rid of all spaces in the column

[jmowreader]

21. You have Word too, right?

And this is a single column of numbers? With no spaces in the numbers themselves?

Export to ASCII text. Open text in Word. Run find/change. In the find box put one space. Leave the "change to" box empty. Click "change all" until you get "no changes made." Save, open in Excel, go to town.

[Ptah]

22. Why not format the column as number, not text?

[NewJeffCT]

23. I did that

I also multiplied the entire column by 1, which usually works, and that didn't, either.

my problem was solved above, though.