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?

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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=536691

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.

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.




"Gazzr" wrote:


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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=536691

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?


"Harlan Grove" wrote:

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.

Hi,

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

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

--

KL Cheong Wrote:
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?


"Harlan Grove" wrote:

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.




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536691

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



KL Cheong wrote:

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.



"Gazzr" wrote:


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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=536691



--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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



KL Cheong wrote:

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.



"Gazzr" wrote:


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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile:
http://www.excelforum.com/member.php...o&userid=31075
View this thread:
http://www.excelforum.com/showthread...hreadid=536691



--

Dave Peterson