Trim Leading Spaces

[Guest]

How do I trim the just the leading and trailing spaces in a cell?
For example

" Project - 1234 " becomes "Project - 1234"

Thank you for you help.

Steven

 

[Dave Peterson]

All in one column?

Select that column
Data|Text to columns
Fixed width
remove any lines that excel guessed

Finish up.

 

[Guest]

=TRIM(A1) etc.

Dave

 

[Guest]

Using trim leaves a space between the quotes and the first and last letter so
a bit more complicated but try

=CONCATENATE("""",TRIM(MID(A15,2,LEN(A15)-2)),"""")

where your string is in A15

 

[Guest]

There are not actually quotes in the string. I just have them to show there
are spaces in front and back. Also, the number of spaces can vary. The
first record may have 3 spaces in front and the second 5 and so on.....

' This is record one '
' This is record two '
'This is record three '

There are not really ' in the cell. I just included them to show all the
blank spaces.

How using a formula would you take the leading and trailing spaces away?

Thank you,

Steven

 

[Guest]

I forgot to mention:

If I just put an example in excel with spaces then the text then spaces and
test with Trim(CellAddress) then it will trim properly.

This was something taken off the internet. I have see in other posts where
you have to test the characters but I have not been able to make those work.
It is the leading spaces that seem to cause the problem.

Steven

 

[Guest]

They probably aren't spaces but some other character which doesn't display.
Try copying one of the "leading spaces" and then use Edit > Replace on your
range of cells replacing the offending character with nothing [just paste
your charcater into the "find" box]