Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
string manipulation, pulling out last name from column | Excel Worksheet Functions | |||
Text string manipulation... | Excel Worksheet Functions | |||
String Manipulation within VBA | Excel Discussion (Misc queries) | |||
String Manipulation | Excel Discussion (Misc queries) | |||
String Manipulation | Excel Programming |