Removing Space

[Guest]

Hi,

I have a list of part number (consist of either 8 or 14 characters) in one
column. However, I notice that each of them are ended with a space after the
last character. Any idea of how to remove the space in a smart way. I have
thousand of lines wonder when I can have all the space removed if going one
by one?

 

[Gazzr]

Hi there,

You could use the =trim(A1) function where A1 is the cell that contains
the text/number you want to remove the leading/following spaces from.

You could also use Edit > Find > Replace menu option. Find: " " (a
space) and Replace with "" (nothing!)

Thanks
Garry

 

[Harlan Grove]

KL Cheong wrote...

I have a list of part number (consist of either 8 or 14 characters) in one
column. However, I notice that each of them are ended with a space after the
last character. Any idea of how to remove the space in a smart way. I have
thousand of lines wonder when I can have all the space removed if going one
by one?

Select the column of data, run Data > Text to Columns, select Fixed
Width, click Next, clear all the field boundaries, then click Finish.
This will preserve all spaces between initial and final nonspace
characters while removing all leading and trailing spaces. Neither TRIM
nor Edit > Replace space with nothing would leave interior spaces as-is.

 

[Guest]

I tried to use those methods you proposed, but they are not working.

My part numbers are as follow,
B09905A005*
75CSM55092A026*
.......

where * is a space.

Could you verify?

Thanks.

 

[Guest]

Since the part number can be 8 or 14 characters, if I clear the boundary
based on 14 characters, then there will be trailing spaces for those consist
of 8 characters, and if based on 8 characters, those with 14 characters will
be truncated. Any idea how to resolve this?

 

[Bryan Hessey]

Hi,

Based on what Harlan said, dont set a boundary, clear any boundar
shown and let the Text-to-Columns create a single column.
As tested, this preserves spaces within the data whilst removin
trailing spaces ( ie ABC***123* will become ABC***123 )

This is a feature that Trim cannot emulate, but it certainly works.

--

 

[Dave Peterson]

If selecting the column
then Edit|replace
what: (spacebar)
with: (leave blank)
replace all

didn't work, then maybe those trailing characters aren't space characters.

If you copied from a web page, you could have those non-breaking HTML spaces
(char(160)'s) in your data.

You could try this:
Select the column
edit|Replace
what: hit and hold the alt key while typing 0160 on the numeric keypad
with: (leave blank)
replace all

If you have to do this kind of stuff lots of times, you may want to try David
McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Biff]

I have David's TrimAll macro attached to a button on one of my toolbars. If
you do a lot of copy/pasting from the web this is a real timesaver. (read: A
MUST)

Biff