Ho to remove spaces before values?
 

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

Ho to remove spaces before values?
 

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

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

"Lion2004" wrote:

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

Ho to remove spaces before values?
 

Select column then DataText to ColumnsFixed WidthNext.
Remove any lines then click Finish

On 9 Mar, 11:39, Lion2004 wrote:
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

Ho to remove spaces before values?
 

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


"Mike" wrote:

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

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

"Lion2004" wrote:

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

Ho to remove spaces before values?
 

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

PlayingToAudienceOfOne wrote:

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

"Mike" wrote:

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

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

"Lion2004" wrote:

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

--

Dave Peterson