How do I remove a space infront of text in a cell?

[Guest]

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?

 

[Trevor Shuttleworth]

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

 

[Biff]

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

 

[Guest]

Try

=CLEAN(A1)

 

[James Silverton]

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

 

[joeu2004]

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

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.

 

[James Silverton]

Hello, (e-mail address removed)!
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

 

[Ron Rosenfeld]

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.


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