Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleaning data | Excel Worksheet Functions | |||
Cleaning up data -- any way to globally fix this?? | Excel Discussion (Misc queries) | |||
Cleaning Data | Excel Discussion (Misc queries) | |||
Cleaning Up Data | Excel Discussion (Misc queries) | |||
Cleaning Product Code list | Excel Worksheet Functions |