VBA Code -- Cleaning Data
 

I have some code that is supposed to clean data in a
particular cell. The data is part numbers. Example...

A1 B1
P/N 1234-5 P/N 12345

When you enter the function cleanse(A1) in B1, the cleaned
part number will appear. It took out the dash. There are
other characters I want to do this with. Here is the code
I have....some of it may be right or wrong. If someone
could point me in the right direction on this code, I
would very much appreciate it...

Sub cleanse()

Dim loc As range
Dim block As range

Set loc = ActiveCell
loc.Select

Set block = Selection

For Each Item In Selection
ActiveCell.Replace What:="P/N", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="(FINE)", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next Item

End Sub




thanks for any help...

Craig

VBA Code -- Cleaning Data
 

You can use a formula to do this
=SUBSTITUTE(SUBSTITUTE(G3,"P/N",""),"-","")

or correct the c.value for ONE line

Sub cleanit()
For Each c In Selection
c.Value = Application.Substitute(Substitute(Range(c.Address) , "P/N", ""),
"-", "")
'=SUBSTITUTE(SUBSTITUTE(G3,"P/N",""),"-","")
Next
End Sub
--
Don Guillett
SalesAid Software

"Craig" wrote in message
...
I have some code that is supposed to clean data in a
particular cell. The data is part numbers. Example...

A1 B1
P/N 1234-5 P/N 12345

When you enter the function cleanse(A1) in B1, the cleaned
part number will appear. It took out the dash. There are
other characters I want to do this with. Here is the code
I have....some of it may be right or wrong. If someone
could point me in the right direction on this code, I
would very much appreciate it...

Sub cleanse()

Dim loc As range
Dim block As range

Set loc = ActiveCell
loc.Select

Set block = Selection

For Each Item In Selection
ActiveCell.Replace What:="P/N", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="(FINE)", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next Item

End Sub




thanks for any help...

Craig

VBA Code -- Cleaning Data
 

You can adapt this function by Leo Heuser to remove any characters
that you want. Just change what's inside the array.

Function ReplaceIllegalChars(Filename As String) As String
'' Replaces illegal filename characters with a space.

, 5. August 2001
Dim Illegal As Variant
Dim Counter As Integer

Illegal = Array("<", "", "?", "[", "]", ":", "|", "*", "/")

For Counter = LBound(Illegal) To UBound(Illegal)
Do While InStr(Filename, Illegal(Counter))
Mid(Filename, InStr(Filename, Illegal(Counter)), 1) = " "
Loop
Next Counter

ReplaceIllegalChars = Filename

End Function

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

I have some code that is supposed to clean data in a
particular cell. The data is part numbers. Example...

A1 B1
P/N 1234-5 P/N 12345

When you enter the function cleanse(A1) in B1, the cleaned
part number will appear. It took out the dash. There are
other characters I want to do this with.