string manipulation
 

I want to write a function called cleanse(). I want it to
work the way the sqrt() function works, as in you put it
in a cell (such as B1) and it works on whatever cell is
entered as the parameter (such as A1).

This function (if =cleanse(A1) were entered in cell B1,
for instance) would take what is in A1 (maybe a part #
like N-11-4) and take out all specified characters and put
the cleansed part number in B1 (looking like N114).

Can someone please give me some guidance on how this would
be accomplished? Below is what I have so far....it puts 0
in the cell, but does not do what I want...any help would
be appreciated...


Function Cleanse(the_cell)

For Each the_cell 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
ActiveCell.Replace What:="(ALT)", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next the_cell

End Function

string manipulation
 

Craig

one way:

Function Cleanse(the_cell As Range) As Variant

' use substitute to replace values
' SUBSTITUTE(text,old_text,new_text)

Cleanse = the_cell.Value

Cleanse = Application.WorksheetFunction.Substitute(Cleanse, "P/N", "")
Cleanse = Application.WorksheetFunction.Substitute(Cleanse, "(FINE)", "")
Cleanse = Application.WorksheetFunction.Substitute(Cleanse, "(ALT)", "")
Cleanse = Application.WorksheetFunction.Substitute(Cleanse, "-", "")

End Function


I think you can only do one cell at a time.

Regards

Trevor


"Craig" wrote in message
...
I want to write a function called cleanse(). I want it to
work the way the sqrt() function works, as in you put it
in a cell (such as B1) and it works on whatever cell is
entered as the parameter (such as A1).

This function (if =cleanse(A1) were entered in cell B1,
for instance) would take what is in A1 (maybe a part #
like N-11-4) and take out all specified characters and put
the cleansed part number in B1 (looking like N114).

Can someone please give me some guidance on how this would
be accomplished? Below is what I have so far....it puts 0
in the cell, but does not do what I want...any help would
be appreciated...


Function Cleanse(the_cell)

For Each the_cell 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
ActiveCell.Replace What:="(ALT)", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next the_cell

End Function

string manipulation
 

Craig,

Approach below is to specifically include characters.

Public Function Cleanse(InVal As String)
Dim i As Long, strTemp As String

For i = 1 To Len(InVal)
Select Case Mid(InVal, i, 1)
Case "A" To "Z", "a" To "z", "0" To "9": strTemp = strTemp &
Mid(InVal, i, 1)
End Select
Next
Cleanse = strTemp
End Function


If you want to specifically exclude chars:
Select Case Mid(InVal, i, 1)
Case "-"
Case Else: strTemp = strTemp & Mid(InVal, i, 1)
End Select


Rob

"Craig" wrote in message
...
I want to write a function called cleanse(). I want it to
work the way the sqrt() function works, as in you put it
in a cell (such as B1) and it works on whatever cell is
entered as the parameter (such as A1).

This function (if =cleanse(A1) were entered in cell B1,
for instance) would take what is in A1 (maybe a part #
like N-11-4) and take out all specified characters and put
the cleansed part number in B1 (looking like N114).

Can someone please give me some guidance on how this would
be accomplished? Below is what I have so far....it puts 0
in the cell, but does not do what I want...any help would
be appreciated...


Function Cleanse(the_cell)

For Each the_cell 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
ActiveCell.Replace What:="(ALT)", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next the_cell

End Function