Blank space in Excel Cell 1

[camster39]

Ran a large export from a customer database to MSExcel 2002. Every cell in one of my columns has an extra space in it which is messing up my calculations. For example the cell should show '48993' but instead the true value of the cell is '48993 ' (a space after the 3).

I tried formatting the cell as number, formatting the cell as custom with type = "#####" and also with type = "00000". Nothing seems to get rid of this space. Then I tried to find " " and replace with "" but it didn't find anything. How can I get rid of this space?

Thank you.

 

[bluedragon2]

Take a look at the TRIM function.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Assuming that you want to end up with the cells formatted as numbers, then in a cell outside of the imported range, type "1." Make sure it defaults to right-alignment (This indicates that Excel has accepted it as a number rather than text).

[ul][li]Copy that cell[/li][li]Highlight the range that you want to change to number format (to get rid of the trailing space)[/li][li]Right click[/li][li]Under 'Operation', choose 'multiply'[/li][li]Click 'OK'[/li][/ul]

NOTE: If there are null cells in the range, they will be converted to zeros

Hope that helps,
John

 

[camster39]

Thanks for the suggestions. I already tried the Trim and multiply paste trick but I didn't have much luck. Here's another suggestion that I got that worked for me.

___________________________________________________________

»

http://www.webmasterworld.com/forum10/3711.htm

Place your cursor in one of the number cells.. select that little space to the left of the number with your cursor and hit Ctrl+C to copy it.

From the menu select 'Edit' 'Find' 'Replace'.

In the 'find' box, hit Ctrl+V to paste your little blank space that you copied (it's not a normal space, so space bar doesn't work - hence copying it).

In the Replace box put nothing.

Select the option to replace by columns (as opposed to rows). Your cursor should still be in the number column.

Hit 'replace all' and watch them lil suckers fly over to the right like real numbers