Solved: Deleteing indertiminate spaces

[khalid79m]

I have in column A file names, eg

Joe Bloggs_Learning_Team <spaces end here
Sam Smith_Artist_Patch <spaces end here
Zen Hen_Clouring_Group <space end here

The file names have interiminate white space at the end of them how can I get rid of it?

[khalid79m]

I have also found out that the data base which gives me this data, is set to allow upto 60 charachters for the file name, if they dont get filled then it automatically leaves the blank spaces

[JONvdHeyden]

You could try the TRIM function:

=TRIM(A1)

[GTO]

Probably not the best idea, but if it's something that TRIM or Trim or Replace does not take care of, a quick/easy check that may be fruitful is to check an see if you can return the cahracter code. With the string planted in A1...

=CODE(RIGHT(A1,1))

Mark

[mdmackillop]

There are a couple of KB items that relate to this. KBSearch

[Paul_Hossler]

I use a user function that (so far) cleans all the funny characters, and extra spaces


[vba]
'Ref: MS Excel 2007 Help, Remove spaces and nonprinting characters from text
Function SuperClean(s As String) As String
Dim s1 As String

With Application.WorksheetFunction

'non-printable characters NOT caught by CLEAN
s1 = .Substitute(s, Chr(160), Chr(32)) ' non-breaking space
s1 = .Substitute(s1, Chr(127), Chr(7)) ' ASCII 7 = BEL char
s1 = .Substitute(s1, Chr(129), Chr(7))
s1 = .Substitute(s1, Chr(141), Chr(7))
s1 = .Substitute(s1, Chr(143), Chr(7))
s1 = .Substitute(s1, Chr(144), Chr(7))
s1 = .Substitute(s1, Chr(157), Chr(7))
'remove leading, trailing, multiple spaces (inc. what was 160's)
s1 = .Trim(s1)
'remove 0 - 31 (inc. what was 127, 129, 141, 143, 144, and 157)
SuperClean = .Clean(s1)
End With
End Function
[/vba]

Paul

[Kenneth Hobs]

Here is a quick example. You could replace my Trim() with Paul's SuperClean if you need more cleanup.

Of course JON's method is easy enough. Put that formula in B1 and copy down. Then paste that column to A1. One typically inserts a column to do the scratch work.

[vba]Sub TrimSpaces()
Dim cell As Range
[a1] = "ken "
[a2] = " ken"
[a3] = " ken "
[a4] = "ken"

For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
cell.Value = Trim(cell.Value)
Next cell
End Sub[/vba]

Mdmackillop's KB file is corrupted since all KB zip files were corrupted after the forum was restored. Zack is working on a plan to get those entries replaced but it will take some work.

[khalid79m]

The Trim Function worked fine

[vzachin]

hi
this is how i usually do it:
select the column,
then Data->Text to Columns ->Fixed Width ->remove all column breaks
this will remove all the spaces at the end

zach

[khalid79m]

thanks for the last post that worked a treat