Getting rid of char(160)

[Biff]

Hi Folks!

I recorded a macro to clean the range selected of char(160)'s and attached
it to a button on one of my toolbars.

It's not working. Here's the code from the macro recorder:

Sub Clean160()

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

The actual recorded code included as the first line:

Range("A1:A10").Select

I figured since that referenced a specific range I could just remove that
line and since the next line starts with Selection, that referred to any
range selected. Is that the problem?

Also, how does the code:

Selection.Replace What:=" ",

know that " " is char(160) and not char(32)?

I tried replacing =" " with =char(160) but that errored.

Thanks!

Biff

[Chip Pearson]

Biff,

Try
Selection.Replace what:=Chr(160), replacement:=" "

or in the Replace dialog box, hold down the ALT key and type 0160
on the numeric keypad (not the number keys above the letters).



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Biff" <biffinpitt@comcast.net> wrote in message
news:%23aiX7p9QGHA.2300@TK2MSFTNGP11.phx.gbl...
> Hi Folks!
>
> I recorded a macro to clean the range selected of char(160)'s
> and attached it to a button on one of my toolbars.
>
> It's not working. Here's the code from the macro recorder:
>
> Sub Clean160()
>
> Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart,
> _
> SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False, _
> ReplaceFormat:=False
>
> End Sub
>
> The actual recorded code included as the first line:
>
> Range("A1:A10").Select
>
> I figured since that referenced a specific range I could just
> remove that line and since the next line starts with Selection,
> that referred to any range selected. Is that the problem?
>
> Also, how does the code:
>
> Selection.Replace What:=" ",
>
> know that " " is char(160) and not char(32)?
>
> I tried replacing =" " with =char(160) but that errored.
>
> Thanks!
>
> Biff
>
>
>

[Biff]

Thanks, Chip!

Selection.Replace what:=Chr(160)

Worked!

Biff

"Chip Pearson" <chip@cpearson.com> wrote in message
news:elRbG79QGHA.4976@TK2MSFTNGP11.phx.gbl...
> Biff,
>
> Try
> Selection.Replace what:=Chr(160), replacement:=" "
>
> or in the Replace dialog box, hold down the ALT key and type 0160 on the
> numeric keypad (not the number keys above the letters).
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:%23aiX7p9QGHA.2300@TK2MSFTNGP11.phx.gbl...
>> Hi Folks!
>>
>> I recorded a macro to clean the range selected of char(160)'s and
>> attached it to a button on one of my toolbars.
>>
>> It's not working. Here's the code from the macro recorder:
>>
>> Sub Clean160()
>>
>> Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>> ReplaceFormat:=False
>>
>> End Sub
>>
>> The actual recorded code included as the first line:
>>
>> Range("A1:A10").Select
>>
>> I figured since that referenced a specific range I could just remove that
>> line and since the next line starts with Selection, that referred to any
>> range selected. Is that the problem?
>>
>> Also, how does the code:
>>
>> Selection.Replace What:=" ",
>>
>> know that " " is char(160) and not char(32)?
>>
>> I tried replacing =" " with =char(160) but that errored.
>>
>> Thanks!
>>
>> Biff
>>
>>
>>
>
>

[tmcaridi]

I use a simple formula:

=TRIM(SUBSTITUTE(cell reference,CHAR(160),CHAR(32)))

[mburns_08109]

Biff (and others):

If working in VBA use the chr() function
If working in an Excel worksheet formula, use the CHAR() function

They are basically identical functions, but operate in difference contexts.