Ho to remove spaces before values?

[Guest]

Hi!
I have document with different values and some of them has spaces before
value. Text was imported into excel!
Is it possible somehow remove automatically all spaces from the beginnng of
the all cells?
Thank you!

Peter

 

[Guest]

Use TRIM to remove all spaces except those between words i.e.

=Trim(A1) would remove all leading and trailing spaces

 

[Lori]

Select column then Data>Text to Columns>Fixed Width>Next.
Remove any lines then click Finish

 

[Guest]

=Trim(values) doesn't seem to work for me... hmmmmm

 

[Dave Peterson]

How did it not work?

Maybe what looks like spaces aren't really spaces????

You may want to try a macro from David McRitchie. Depending on what's in the
cell, it may work for you.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If that doesn't work...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Then you can either fix it via a helper cell or a macro:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm