software

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 gt; Find gt; Replace menu option. Find: quot; quot; (a
space) and Replace with quot;quot; (nothing!)

Thanks
Garry--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: www.excelforum.com/member.php...oamp;userid=31075
View this thread: www.excelforum.com/showthread...hreadid=536691KL Cheong wrote...
gt;I have a list of part number (consist of either 8 or 14 characters) in one
gt;column. However, I notice that each of them are ended with a space after the
gt;last character. Any idea of how to remove the space in a smart way. I have
gt;thousand of lines wonder when I can have all the space removed if going one
gt;by one?

Select the column of data, run Data gt; 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 gt; 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.

quot;Gazzrquot; wrote:

gt;
gt; Hi there,
gt;
gt; You could use the =trim(A1) function where A1 is the cell that contains
gt; the text/number you want to remove the leading/following spaces from.
gt;
gt; You could also use Edit gt; Find gt; Replace menu option. Find: quot; quot; (a
gt; space) and Replace with quot;quot; (nothing!)
gt;
gt; Thanks
gt; Garry
gt;
gt;
gt; --
gt; Gazzr
gt; ------------------------------------------------------------------------
gt; Gazzr's Profile: www.excelforum.com/member.php...oamp;userid=31075
gt; View this thread: www.excelforum.com/showthread...hreadid=536691
gt;
gt;

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?quot;Harlan Grovequot; wrote:

gt; KL Cheong wrote...
gt; gt;I have a list of part number (consist of either 8 or 14 characters) in one
gt; gt;column. However, I notice that each of them are ended with a space after the
gt; gt;last character. Any idea of how to remove the space in a smart way. I have
gt; gt;thousand of lines wonder when I can have all the space removed if going one
gt; gt;by one?
gt;
gt; Select the column of data, run Data gt; Text to Columns, select Fixed
gt; Width, click Next, clear all the field boundaries, then click Finish.
gt; This will preserve all spaces between initial and final nonspace
gt; characters while removing all leading and trailing spaces. Neither TRIM
gt; nor Edit gt; Replace space with nothing would leave interior spaces as-is.
gt;
gt;


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:
gt; Since the part number can be 8 or 14 characters, if I clear the
gt; boundary
gt; based on 14 characters, then there will be trailing spaces for those
gt; consist
gt; of 8 characters, and if based on 8 characters, those with 14 characters
gt; will
gt; be truncated. Any idea how to resolve this?
gt;
gt;
gt; quot;Harlan Grovequot; wrote:
gt;
gt; gt; KL Cheong wrote...
gt; gt; gt;I have a list of part number (consist of either 8 or 14 characters)
gt; in one
gt; gt; gt;column. However, I notice that each of them are ended with a space
gt; after the
gt; gt; gt;last character. Any idea of how to remove the space in a smart way.
gt; I have
gt; gt; gt;thousand of lines wonder when I can have all the space removed if
gt; going one
gt; gt; gt;by one?
gt; gt;
gt; gt; Select the column of data, run Data gt; Text to Columns, select Fixed
gt; gt; Width, click Next, clear all the field boundaries, then click
gt; Finish.
gt; gt; This will preserve all spaces between initial and final nonspace
gt; gt; characters while removing all leading and trailing spaces. Neither
gt; TRIM
gt; gt; nor Edit gt; Replace space with nothing would leave interior spaces
gt; as-is.
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=536691If 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:

www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for quot;Sub Trimall()quot;

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm
KL Cheong wrote:
gt;
gt; I tried to use those methods you proposed, but they are not working.
gt;
gt; My part numbers are as follow,
gt; B09905A005*
gt; 75CSM55092A026*
gt; ......
gt;
gt; where * is a space.
gt;
gt; Could you verify?
gt;
gt; Thanks.
gt;
gt;
gt;
gt; quot;Gazzrquot; wrote:
gt;
gt; gt;
gt; gt; Hi there,
gt; gt;
gt; gt; You could use the =trim(A1) function where A1 is the cell that contains
gt; gt; the text/number you want to remove the leading/following spaces from.
gt; gt;
gt; gt; You could also use Edit gt; Find gt; Replace menu option. Find: quot; quot; (a
gt; gt; space) and Replace with quot;quot; (nothing!)
gt; gt;
gt; gt; Thanks
gt; gt; Garry
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Gazzr
gt; gt; ------------------------------------------------------------------------
gt; gt; Gazzr's Profile: www.excelforum.com/member.php...oamp;userid=31075
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=536691
gt; gt;
gt; gt;

--

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

quot;Dave Petersonquot; gt; wrote in message
...
gt; If selecting the column
gt; then Edit|replace
gt; what: (spacebar)
gt; with: (leave blank)
gt; replace all
gt;
gt; didn't work, then maybe those trailing characters aren't space characters.
gt;
gt; If you copied from a web page, you could have those non-breaking HTML
gt; spaces
gt; (char(160)'s) in your data.
gt;
gt; You could try this:
gt; Select the column
gt; edit|Replace
gt; what: hit and hold the alt key while typing 0160 on the numeric keypad
gt; with: (leave blank)
gt; replace all
gt;
gt; If you have to do this kind of stuff lots of times, you may want to try
gt; David
gt; McRitchie's routine to clean the data:
gt;
gt; www.mvps.org/dmcritchie/excel/join.htm#trimall
gt; (look for quot;Sub Trimall()quot;
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt;
gt;
gt; KL Cheong wrote:
gt;gt;
gt;gt; I tried to use those methods you proposed, but they are not working.
gt;gt;
gt;gt; My part numbers are as follow,
gt;gt; B09905A005*
gt;gt; 75CSM55092A026*
gt;gt; ......
gt;gt;
gt;gt; where * is a space.
gt;gt;
gt;gt; Could you verify?
gt;gt;
gt;gt; Thanks.
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Gazzrquot; wrote:
gt;gt;
gt;gt; gt;
gt;gt; gt; Hi there,
gt;gt; gt;
gt;gt; gt; You could use the =trim(A1) function where A1 is the cell that contains
gt;gt; gt; the text/number you want to remove the leading/following spaces from.
gt;gt; gt;
gt;gt; gt; You could also use Edit gt; Find gt; Replace menu option. Find: quot; quot; (a
gt;gt; gt; space) and Replace with quot;quot; (nothing!)
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt; Garry
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Gazzr
gt;gt; gt; ------------------------------------------------------------------------
gt;gt; gt; Gazzr's Profile:
gt;gt; gt; www.excelforum.com/member.php...oamp;userid=31075
gt;gt; gt; View this thread:
gt;gt; gt; www.excelforum.com/showthread...hreadid=536691
gt;gt; gt;
gt;gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson