Prev: Calculate Dates
Next: Sorting whole Protected 2003 REDUX
From: cas on 29 Mar 2010 09:30
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.
From: Faraz Ahmed Qureshi on 29 Mar 2010 09:36
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 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.
From: Luke M on 29 Mar 2010 09:37
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" <cas(a)discussions.microsoft.com> wrote in message
news:91366F03-9733-45AA-AA18-1DB18394F7E4(a)microsoft.com...
>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.


From: Don Guillett on 29 Mar 2010 10:02
try

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"cas" <cas(a)discussions.microsoft.com> wrote in message
news:91366F03-9733-45AA-AA18-1DB18394F7E4(a)microsoft.com...
>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.

From: cas on 1 Apr 2010 02:38
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 didn't 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 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.
 | 
Pages: 1
Prev: Calculate Dates
Next: Sorting whole Protected 2003 REDUX