Using Excel 2007
Is there a way to modify the CLEAN function to replace the non-printable 
character with a space?
As it works now, the words come out right next to each other and we still 
have to "clean" it.

Thanks

Look here:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Trainer" <asaul@leydig.com> wrote in message 
news:ON%23BN9HZKHA.1592@TK2MSFTNGP06.phx.gbl...
> Using Excel 2007
> Is there a way to modify the CLEAN function to replace the non-printable 
> character with a space?
> As it works now, the words come out right next to each other and we still 
> have to "clean" it.
>
> Thanks
>
> 

Thank you for that link.  However, it does not tell me how to replace the 
non-printable characters with a space, or if so, I didn't see that.  Can you 
explain it to me simply?  Thank you.


"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:unvjGMIZKHA.4920@TK2MSFTNGP04.phx.gbl...
> Look here:
>
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>
> -- 
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Trainer" <asaul@leydig.com> wrote in message 
> news:ON%23BN9HZKHA.1592@TK2MSFTNGP06.phx.gbl...
>> Using Excel 2007
>> Is there a way to modify the CLEAN function to replace the non-printable 
>> character with a space?
>> As it works now, the words come out right next to each other and we still 
>> have to "clean" it.
>>
>> Thanks
>>
>>
> 

I don't know what's going on here!
Clicking the link doesn't get me to the page of which I copied the address.
Try copying this into your browswe  and then remove the initial apostrophe

'http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Otherwise, visit Davids site and locate the page yourself. It is about your 
subject!

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Trainer" <asaul@leydig.com> wrote in message 
news:eiIPzWIZKHA.5108@TK2MSFTNGP06.phx.gbl...
> Thank you for that link.  However, it does not tell me how to replace the 
> non-printable characters with a space, or if so, I didn't see that.  Can 
> you explain it to me simply?  Thank you.
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message 
> news:unvjGMIZKHA.4920@TK2MSFTNGP04.phx.gbl...
>> Look here:
>>
>> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>>
>> -- 
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "Trainer" <asaul@leydig.com> wrote in message 
>> news:ON%23BN9HZKHA.1592@TK2MSFTNGP06.phx.gbl...
>>> Using Excel 2007
>>> Is there a way to modify the CLEAN function to replace the non-printable 
>>> character with a space?
>>> As it works now, the words come out right next to each other and we 
>>> still have to "clean" it.
>>>
>>> Thanks
>>>
>>>
>>
>
> 

Try edit>replace

What:  Alt + 0160   (use the numpad to enter the digits)

With:   space

Here is David's trimall macro.

Sub TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace what:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each Cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     Cell.Value = Application.Trim(Cell.Value)
   Next Cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub


Gord Dibben  MS Excel MVP

On Fri, 13 Nov 2009 10:35:22 -0600, "Trainer" <asaul@leydig.com> wrote:

>Using Excel 2007
>Is there a way to modify the CLEAN function to replace the non-printable 
>character with a space?
>As it works now, the words come out right next to each other and we still 
>have to "clean" it.
>
>Thanks
>

I tried both suggestions, the macro and the edit replace.  That symbol is 
still there.  it looks like a square with a ? inside it.  I think your 
tricks would work if that crazy symbol could be identified.  Thanks for your 
suggestions anyhow.

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:3r6rf59o79767qn6n48ii078f1q1ffk61q@4ax.com...
> Try edit>replace
>
> What:  Alt + 0160   (use the numpad to enter the digits)
>
> With:   space
>
> Here is David's trimall macro.
>
> Sub TrimALL()
>   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
>   Application.ScreenUpdating = False
>   Application.Calculation = xlCalculationManual
>   Dim Cell As Range
>   'Also Treat CHR 0160, as a space (CHR 032)
>   Selection.Replace what:=Chr(160), replacement:=Chr(32), _
>     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
>   'Trim in Excel removes extra internal spaces, VBA does not
>   On Error Resume Next   'in case no text cells in selection
>   For Each Cell In Intersect(Selection, _
>      Selection.SpecialCells(xlConstants, xlTextValues))
>     Cell.Value = Application.Trim(Cell.Value)
>   Next Cell
>   On Error GoTo 0
>   Application.Calculation = xlCalculationAutomatic
>   Application.ScreenUpdating = True
> End Sub
>
>
> Gord Dibben  MS Excel MVP
>
> On Fri, 13 Nov 2009 10:35:22 -0600, "Trainer" <asaul@leydig.com> wrote:
>
>>Using Excel 2007
>>Is there a way to modify the CLEAN function to replace the non-printable
>>character with a space?
>>As it works now, the words come out right next to each other and we still
>>have to "clean" it.
>>
>>Thanks
>>
> 

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it.  Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad.  For example, alt-0010 (or ctrl-j) can be used for linefeeds.  But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

    Dim myBadChars As Variant
    Dim myGoodChars As Variant
    Dim iCtr As Long
    
    myBadChars = Array(Chr(##))  '<--What showed up in CellView?
    
    myGoodChars = Array("")  
    
    If UBound(myGoodChars) <> UBound(myBadChars) Then
        MsgBox "Design error!"
        Exit Sub
    End If
    
    For iCtr = LBound(myBadChars) To UBound(myBadChars)
        ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
            Replacement:=myGoodChars(iCtr), _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False
    Next iCtr
    
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Trainer wrote:
> 
> I tried both suggestions, the macro and the edit replace.  That symbol is
> still there.  it looks like a square with a ? inside it.  I think your
> tricks would work if that crazy symbol could be identified.  Thanks for your
> suggestions anyhow.
> 
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:3r6rf59o79767qn6n48ii078f1q1ffk61q@4ax.com...
> > Try edit>replace
> >
> > What:  Alt + 0160   (use the numpad to enter the digits)
> >
> > With:   space
> >
> > Here is David's trimall macro.
> >
> > Sub TrimALL()
> >   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
> >   Application.ScreenUpdating = False
> >   Application.Calculation = xlCalculationManual
> >   Dim Cell As Range
> >   'Also Treat CHR 0160, as a space (CHR 032)
> >   Selection.Replace what:=Chr(160), replacement:=Chr(32), _
> >     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
> >   'Trim in Excel removes extra internal spaces, VBA does not
> >   On Error Resume Next   'in case no text cells in selection
> >   For Each Cell In Intersect(Selection, _
> >      Selection.SpecialCells(xlConstants, xlTextValues))
> >     Cell.Value = Application.Trim(Cell.Value)
> >   Next Cell
> >   On Error GoTo 0
> >   Application.Calculation = xlCalculationAutomatic
> >   Application.ScreenUpdating = True
> > End Sub
> >
> >
> > Gord Dibben  MS Excel MVP
> >
> > On Fri, 13 Nov 2009 10:35:22 -0600, "Trainer" <asaul@leydig.com> wrote:
> >
> >>Using Excel 2007
> >>Is there a way to modify the CLEAN function to replace the non-printable
> >>character with a space?
> >>As it works now, the words come out right next to each other and we still
> >>have to "clean" it.
> >>
> >>Thanks
> >>
> >

-- 

Dave Peterson

This addin is fantastic.  Thank you very much.

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4AFDB300.6618355D@verizonXSPAM.net...
> Saved from a previous post.
>
> Chip Pearson has a very nice addin that will help determine what that
> character(s) is:
> http://www.cpearson.com/excel/CellView.aspx
>
> Depending on what that character is, you may be able to use alt-#### (from 
> the
> number keypad) to enter the character into the Other box in the text to 
> columns
> wizard dialog.
>
> In fact, you may be able to select the character (in the formula bar), and 
> copy
> it.  Then use ctrl-v to paste into that text to columns Other box.
>
> You may be able to use Edit|Replace to change the character--Some 
> characters can
> be entered by holding the alt-key and typing the hex number on the numeric
> keypad.  For example, alt-0010 (or ctrl-j) can be used for linefeeds.  But 
> I've
> never been able to get alt-0013 to work for carriage returns.
>
> Another alternative is to fix it via a formula:
>
> =substitute(a1,char(##),"")
>
> Replace ## with the ASCII value you see in Chip's addin.
>
> Or you could use a macro (after using Chip's CellView addin):
>
> Option Explicit
> Sub cleanEmUp()
>
>    Dim myBadChars As Variant
>    Dim myGoodChars As Variant
>    Dim iCtr As Long
>
>    myBadChars = Array(Chr(##))  '<--What showed up in CellView?
>
>    myGoodChars = Array("")
>
>    If UBound(myGoodChars) <> UBound(myBadChars) Then
>        MsgBox "Design error!"
>        Exit Sub
>    End If
>
>    For iCtr = LBound(myBadChars) To UBound(myBadChars)
>        ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
>            Replacement:=myGoodChars(iCtr), _
>            LookAt:=xlPart, SearchOrder:=xlByRows, _
>            MatchCase:=False
>    Next iCtr
>
> End Sub
>
> If you're new to macros:
>
> Debra Dalgleish has some notes how to implement macros here:
> http://www.contextures.com/xlvba01.html
>
> David McRitchie has an intro to macros:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Ron de Bruin's intro to macros:
> http://www.rondebruin.nl/code.htm
>
> (General, Regular and Standard modules all describe the same thing.)
>
> Trainer wrote:
>>
>> I tried both suggestions, the macro and the edit replace.  That symbol is
>> still there.  it looks like a square with a ? inside it.  I think your
>> tricks would work if that crazy symbol could be identified.  Thanks for 
>> your
>> suggestions anyhow.
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news:3r6rf59o79767qn6n48ii078f1q1ffk61q@4ax.com...
>> > Try edit>replace
>> >
>> > What:  Alt + 0160   (use the numpad to enter the digits)
>> >
>> > With:   space
>> >
>> > Here is David's trimall macro.
>> >
>> > Sub TrimALL()
>> >   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
>> >   Application.ScreenUpdating = False
>> >   Application.Calculation = xlCalculationManual
>> >   Dim Cell As Range
>> >   'Also Treat CHR 0160, as a space (CHR 032)
>> >   Selection.Replace what:=Chr(160), replacement:=Chr(32), _
>> >     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
>> >   'Trim in Excel removes extra internal spaces, VBA does not
>> >   On Error Resume Next   'in case no text cells in selection
>> >   For Each Cell In Intersect(Selection, _
>> >      Selection.SpecialCells(xlConstants, xlTextValues))
>> >     Cell.Value = Application.Trim(Cell.Value)
>> >   Next Cell
>> >   On Error GoTo 0
>> >   Application.Calculation = xlCalculationAutomatic
>> >   Application.ScreenUpdating = True
>> > End Sub
>> >
>> >
>> > Gord Dibben  MS Excel MVP
>> >
>> > On Fri, 13 Nov 2009 10:35:22 -0600, "Trainer" <asaul@leydig.com> wrote:
>> >
>> >>Using Excel 2007
>> >>Is there a way to modify the CLEAN function to replace the 
>> >>non-printable
>> >>character with a space?
>> >>As it works now, the words come out right next to each other and we 
>> >>still
>> >>have to "clean" it.
>> >>
>> >>Thanks
>> >>
>> >
>
> -- 
>
> Dave Peterson 

Visit www.ExcelGoodies.Com