Removal of 100's of spacing in numbers
 

I want to be able to use the replace command, but it wont find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. €œ1 234 €œ

I know the use of SUBSTITUTE and other functions, but it is not an effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the replace
command.

Removal of 100's of spacing in numbers
 

Might be some other character.
Place your cursor between 1 and 2 and copy
Select the columns with numbers
Press Ctrl+H
In the Find box press Ctrl+V for the space like character
Leave the Replace With box blank
and press Replace All

Best of luck.
--
Thanx & Best Regards,

Faraz!


"cas" wrote:

I want to be able to use the replace command, but it wont find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. €œ1 234 €œ

I know the use of SUBSTITUTE and other functions, but it is not an effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the replace
command.

Removal of 100's of spacing in numbers
 

It may be some other non-printable character, such as CHAR(160)
You can either try copying the space from your data and then do a
find&replace on that, or you could try and determine what exact character it
is via:
=CODE(MID(A2,2,1))
If it's a true space, it should be code 32.
Check out the XL help topic on CLEAN for more info on non-printable
characters.

--
Best Regards,

Luke M
"cas" wrote in message
...
I want to be able to use the replace command, but it won't find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. "1 234 "

I know the use of SUBSTITUTE and other functions, but it is not an
effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the
replace
command.

Removal of 100's of spacing in numbers
 

try

Sub replacespaces()
Cells.Replace " ", ""
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"cas" wrote in message
...
I want to be able to use the replace command, but it wont find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. €œ1 234 €œ

I know the use of SUBSTITUTE and other functions, but it is not an
effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the
replace
command.

Removal of 100's of spacing in numbers
 

You are totally right. I copied it to Word where I could see that it was
"hard spacing" (don't know the correct word for it in English). A bit ashamed
that I didnt try to copy the character to the replace-box. Maybe a too easy
solution :)

"Faraz Ahmed Qureshi" wrote:

Might be some other character.
Place your cursor between 1 and 2 and copy
Select the columns with numbers
Press Ctrl+H
In the Find box press Ctrl+V for the space like character
Leave the Replace With box blank
and press Replace All

Best of luck.
--
Thanx & Best Regards,

Faraz!


"cas" wrote:

I want to be able to use the replace command, but it wont find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. €œ1 234 €œ

I know the use of SUBSTITUTE and other functions, but it is not an effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the replace
command.