Finding (and eliminating) hidden "space" character

[smartgal]

I downloaded some numeric data but I have discovered that there is a leading
"space" character. I tried a find and replace and a host of other tricks but
short of going into each cell and deleting I'm at a loss for how to bulk find
and replace this. It's preventing me from sorting, etc. I tried to outfox
it by doing a formula and adding the value to "0" but I got an error and when
I asked it to evaluate it gave me the following =" 1069.95"+0) and that's
how I discovered the leading space.

Thanks -

mm

 

[Do what I can, but I'm only one man.]

Here is a macro I use to replace/remove:
1) many many spaces => one
2) Probably your culprit here: the Unicode "character 160" => a normal space.
3) a space & period => just a period .<=corrects this anomoly.
4) and some other annoyances...see below.

Sub TrimNSlenderizeNCleanAll()
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell.Replace What:=" ,", Replacement:=", ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" .", Replacement:=". ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell = Trim(cell)
cell.Value = Application.Clean(cell.Value)
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
cell.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End If
Next cell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

It has taken me maybe a few days to figure that one out manually (i.e.
learning Visual Basic through examples); enjoy!

 

[FSt1]

hi
usually(at least up to 2003) excel strips away leading spaces the same way
it strips away leading zeros, viewing such characters as unnecessary so i
suppect that your data was imported as text or at least excel is seeing it as
text.
try this.
put a 1 in a cell off to the side. copy it. highlight your data then paste
special multiply.

regards
FSt1

 

[ShaneDevenshire]

Hi,

If it is constistently a single character - select the column of data and
choose Data, Text to Columns, pick Fixed width, click Next, click between the
1st and 2nd characters in the Preview area and click Finish. Or click Next
and change the destination cell.

 

[MartinW]

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to Edit>Replace
Find What: Alt+0160
Replace With: Leave this box blank
Click Replace All

Alt 0160 is entered by holding down Alt, type 0160
on the Number pad only then let go of Alt)


HTH
Martin

 

[smartgal]

*Perfect* !! Thanks!

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to Edit>Replace
Find What: Alt+0160
Replace With: Leave this box blank
Click Replace All

Alt 0160 is entered by holding down Alt, type 0160
on the Number pad only then let go of Alt)


HTH
Martin

 

[MartinW]

You're welcome Smartgal, Thanks for posting back.

Regards
Martin

 

[M]

MartinW another similar problem-
M here,

I have similar needs - sometimes the find 160 replace works but on one of my
spread sheets with (cell view shows) leading and trailing 160 characters -
nothing seems to work to extract or otherwise convert this to a "real" number
format - except once by some accident that I can't seem to repeat.
Any suggestions?
I am about to post it as a separate frustrating problem that I need help with.


Thank you,
M

 

[MartinW]

Hi M,

To tidy it up properly you may have to do find and replace
for a normal space as well as the 0160.

With that done copy a fresh blank cell (make sure it is one
that has never had anything in it.)

Then select all of your data, then Edit>Paste Special and check
the Add radio button and OK.

That will convert your text numbers to real numbers.

HTH
Martin