PDA

View Full Version : Solved: Deleteing indertiminate spaces



khalid79m
04-16-2009, 03:30 AM
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
04-16-2009, 03:40 AM
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
04-16-2009, 03:45 AM
You could try the TRIM function:

=TRIM(A1)

GTO
04-16-2009, 03:55 AM
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
04-16-2009, 04:01 AM
There are a couple of KB items that relate to this. KBSearch (http://vbaexpress.com/kb/getarticle.php?kb_id=818)

Paul_Hossler
04-16-2009, 05:57 AM
I use a user function that (so far) cleans all the funny characters, and extra spaces



'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


Paul

Kenneth Hobs
04-16-2009, 06:12 AM
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.

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

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
04-22-2009, 04:49 AM
The Trim Function worked fine :)

vzachin
04-22-2009, 05:03 AM
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
05-19-2009, 05:32 AM
thanks for the last post :) that worked a treat