chr(42) to replace

[mostakimm]

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

This is a great code, but when i replace chr(160) with chr(42) which represents * , every thing is deleted. and regular replace in excel dose not do the job.

How can i delet * from 03-08-10* , and it is a text cell.

Any help please, thank you all in advance.
Marwan

[Peo Sjoblom]

Since * is a wildcard you need to precede it with a tilde ~*
you don't need a macro for that, just press ctrl + h and in the find what
box put ~* and in the replace box put whatever you want to replace with
or you can edit the macro and use

Selection.Replace What:="~*", Replacement:=Chr(32),_



for that part




--

Regards,

Peo Sjoblom


"mostakimm" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
> This is a great code, but when i replace chr(160) with chr(42) which
> represents * , every thing is deleted. and regular replace in excel
> dose not do the job.
>
> How can i delet * from 03-08-10* , and it is a text cell.
>
> Any help please, thank you all in advance.
> Marwan
>
>
> --
> mostakimm
> ------------------------------------------------------------------------
> mostakimm's Profile:
http://www.excelforum.com/member.php...o&userid=20025
> View this thread: http://www.excelforum.com/showthread...hreadid=499385
>

[mostakimm]

Thank you Thank you Thank you Thank you;

[Dave Peterson]

Change
What:=Chr(160)
to
What:=Chr(42)

And rerun the macro???

Chr(32) is the space character. Is that what you want it replaced with?

mostakimm wrote:
>
> 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
>
> This is a great code, but when i replace chr(160) with chr(42) which
> represents * , every thing is deleted. and regular replace in excel
> dose not do the job.
>
> How can i delet * from 03-08-10* , and it is a text cell.
>
> Any help please, thank you all in advance.
> Marwan
>
> --
> mostakimm
> ------------------------------------------------------------------------
> mostakimm's Profile: http://www.excelforum.com/member.php...o&userid=20025
> View this thread: http://www.excelforum.com/showthread...hreadid=499385

--

Dave Peterson

[Dave Peterson]

Do not use this!

(I didn't notice that chr(42) was the asterisk!)

Dave Peterson wrote:
>
> Change
> What:=Chr(160)
> to
> What:=Chr(42)
>
> And rerun the macro???
>
> Chr(32) is the space character. Is that what you want it replaced with?
>
> mostakimm wrote:
> >
> > 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
> >
> > This is a great code, but when i replace chr(160) with chr(42) which
> > represents * , every thing is deleted. and regular replace in excel
> > dose not do the job.
> >
> > How can i delet * from 03-08-10* , and it is a text cell.
> >
> > Any help please, thank you all in advance.
> > Marwan
> >
> > --
> > mostakimm
> > ------------------------------------------------------------------------
> > mostakimm's Profile: http://www.excelforum.com/member.php...o&userid=20025
> > View this thread: http://www.excelforum.com/showthread...hreadid=499385
>
> --
>
> Dave Peterson

--

Dave Peterson