Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
GAC GAC is offline
external usenet poster
 
Posts: 1
Default How do I remove a space infront of text in a cell?

I have an Excel sheet that was created from a text file. It has a space in
each cell before the text. I have tried using the Trim function and it is
not removing the extra space. How can I remove the space without doing it
manually?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,089
Default How do I remove a space infront of text in a cell?

It is, quite possibly, a non display/print character other than space.

Assuming the data is in Cell A1 down, try:

=RIGHT(A1,LEN(A1)-1) if there is one "space" character. Drag the formula
down.

Regards

Trevor


"GAC" wrote in message
...
I have an Excel sheet that was created from a text file. It has a space in
each cell before the text. I have tried using the Trim function and it is
not removing the extra space. How can I remove the space without doing it
manually?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default How do I remove a space infront of text in a cell?

There is a macro at this link that should be able to clean your data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Biff

"GAC" wrote in message
...
I have an Excel sheet that was created from a text file. It has a space in
each cell before the text. I have tried using the Trim function and it is
not removing the extra space. How can I remove the space without doing it
manually?



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 174
Default How do I remove a space infront of text in a cell?

Try

=CLEAN(A1)

"GAC" wrote:

I have an Excel sheet that was created from a text file. It has a space in
each cell before the text. I have tried using the Trim function and it is
not removing the extra space. How can I remove the space without doing it
manually?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 182
Default How do I remove a space infront of text in a cell?

Hello, daddylonglegs!
You wrote on Mon, 9 Oct 2006 15:31:02 -0700:

d =CLEAN(A1)

d "GAC" wrote:

?? I have an Excel sheet that was created from a text file.
?? It has a space in each cell before the text. I have tried
?? using the Trim function and it is not removing the extra
?? space. How can I remove the space without doing it
?? manually?

I tried that and it does not work. CLEAN removes non-printable
characters according to HELP

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 418
Default How do I remove a space infront of text in a cell?

James Silverton wrote:
Hello, daddylonglegs!
You wrote on Mon, 9 Oct 2006 15:31:02 -0700:
=CLEAN(A1)


I tried that and it does not work.


Right. The problem I encounter is when I cut-and-paste a column of
numbers from a posting that I am reading using Google Groups, the
"numbers" are treated as text, and nothing seems to undo that. The
reason: Google Groups has replaced some of the space and tab
characters in the original posting (I have looked at the "original"
source) -- notably the first space or tab -- with char(160), which is a
"non-breaking space" in the ANSI character set.

FYI, one work-around is to cut-and-paste from the "original" source
(click Show Options). Users of other newsreaders might not have this
problem, as long as the non-breaking space character is not in the
"original" source. (It never has been in the postings that I have
seen.)

Another work-around is to cut from the formatted posting (not the
"original" source), paste into a file created using Notepad, then use
Import External Data to bring into Excel.

CLEAN removes non-printable characters according to HELP


Right. And apparently the "non-breaking space" is considered a
printable character, as arguably perhaps it should be just as its
cousin, the normal space character, is.

But this might have nothing to do with the OP's problem since in my
experience, Google Groups always has more than one leading "space",
which is a mix of normal and non-printing space characters.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 182
Default How do I remove a space infront of text in a cell?

Hello, !
You wrote on 9 Oct 2006 16:53:41 -0700:

j James Silverton wrote:
?? Hello, daddylonglegs!
?? You wrote on Mon, 9 Oct 2006 15:31:02 -0700:
?? =CLEAN(A1)
??
?? I tried that and it does not work.

j Right. The problem I encounter is when I cut-and-paste a
j column of numbers from a posting that I am reading using
j Google Groups, the "numbers" are treated as text, and
j nothing seems to undo that. The reason: Google Groups has
j replaced some of the space and tab characters in the
j original posting (I have looked at the "original" source) --
j notably the first space or tab -- with char(160), which is a
j "non-breaking space" in the ANSI character set.

j FYI, one work-around is to cut-and-paste from the "original"
j source (click Show Options). Users of other newsreaders
j might not have this problem, as long as the non-breaking
j space character is not in the "original" source. (It never
j has been in the postings that I have seen.)

j Another work-around is to cut from the formatted posting
j (not the "original" source), paste into a file created using
j Notepad, then use Import External Data to bring into Excel.

?? CLEAN removes non-printable characters according to HELP

j Right. And apparently the "non-breaking space" is
j considered a printable character, as arguably perhaps it
j should be just as its cousin, the normal space character,
j is.

I suppose that if something like PureText does not work, the
more extensive editing features of Word might be used as an
intermediate step.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default How do I remove a space infront of text in a cell?

On 9 Oct 2006 16:53:41 -0700, wrote:

James Silverton wrote:
Hello, daddylonglegs!
You wrote on Mon, 9 Oct 2006 15:31:02 -0700:
=CLEAN(A1)


I tried that and it does not work.


Right. The problem I encounter is when I cut-and-paste a column of
numbers from a posting that I am reading using Google Groups, the
"numbers" are treated as text, and nothing seems to undo that. The
reason: Google Groups has replaced some of the space and tab
characters in the original posting (I have looked at the "original"
source) -- notably the first space or tab -- with char(160), which is a
"non-breaking space" in the ANSI character set.

FYI, one work-around is to cut-and-paste from the "original" source
(click Show Options). Users of other newsreaders might not have this
problem, as long as the non-breaking space character is not in the
"original" source. (It never has been in the postings that I have
seen.)

Another work-around is to cut from the formatted posting (not the
"original" source), paste into a file created using Notepad, then use
Import External Data to bring into Excel.

CLEAN removes non-printable characters according to HELP


Right. And apparently the "non-breaking space" is considered a
printable character, as arguably perhaps it should be just as its
cousin, the normal space character, is.

But this might have nothing to do with the OP's problem since in my
experience, Google Groups always has more than one leading "space",
which is a mix of normal and non-printing space characters.


You can use Regular Expressions to deal with that problem. Although you can
easily implement it in VBA, if the strings are less than 256 characters long,
the simplest method is to download Longre's free morefunc.xll addin from:
http://xcell05.free.fr/

And then use the formula:

=REGEX.MID(A1,"\S.*\b") which will remove all leading and trailing
"white-space" characters, including <tab, <space, <nbsp etc.

If the problem is to convert the value to a real number, then you can use:

=--REGEX.MID(A20,"-?(\d+(\.\d*)?|\.\d+)")

which should extract integer or floating point positive or negative numbers.




--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove text from cell Sam Excel Discussion (Misc queries) 2 July 25th 06 04:06 PM
How can I remove diff. numbers from a cell combined with text Greg Excel Discussion (Misc queries) 5 February 28th 06 08:57 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
How do I set text to top of cell next to wrap text in Excel? Carpenter Gary New Users to Excel 1 October 25th 05 06:26 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"