Finding (and eliminating) hidden "space" character
 

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

Finding (and eliminating) hidden "space" character
 

Here is a macro I use to replace/remove:
1) many many spaces = one
2) Probably your culprit he 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!

"smartgal" wrote:

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

Finding (and eliminating) hidden "space" character
 

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

"smartgal" wrote:

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

Finding (and eliminating) hidden "space" character
 

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.
--
Thanks,
Shane Devenshire


"smartgal" wrote:

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

Finding (and eliminating) hidden "space" character
 

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to EditReplace
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" wrote in message
...
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

Finding (and eliminating) hidden "space" character
 

*Perfect* !! Thanks!

"MartinW" wrote:

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to EditReplace
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" wrote in message
...
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

Finding (and eliminating) hidden "space" character
 

You're welcome Smartgal, Thanks for posting back.

Regards
Martin


"smartgal" wrote in message
...
*Perfect* !! Thanks!

"MartinW" wrote:

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to EditReplace
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" wrote in message
...
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

Finding (and eliminating) hidden "space" character
 

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" wrote:

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to EditReplace
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

Finding (and eliminating) hidden "space" character
 

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 EditPaste Special and check
the Add radio button and OK.

That will convert your text numbers to real numbers.

HTH
Martin

"M" wrote in message
...
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" wrote:

Hi mm,

Sounds like non-breaking spaces. To get rid of them
select all of your data
Go to EditReplace
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