HELP - TO FIND AND REMOVE BITS OF TEXT

[eddievegas]

have a list of numbers which i need to remove
the text characters from,like
485P6
25M2
29306P3
3249RP108
25AM2
need to remove character codes 65 thru 90
somehow? thanks in advance

[ This Message was edited by: eddievegas on 2002-04-08 15:09 ]

[Aladin Akyurek]

On 2002-04-08 15:06, eddievegas wrote:
have a list of numbers which i need to remove
the text characters from,like
485P6
25M2
29306P3
3249RP108
25AM2
need to remove character codes 65 thru 90
somehow? thanks in advance

[ This Message was edited by: eddievegas on 2002-04-08 15:09 ]

Eddie,

Lets assume that A1:A5 houses the sample you provided:

In B1 enter and copy down:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,"")

In C1 enter and copy down:

=SUBSTITUTE(A1,SUBSTITUTE(SUBSTITUTE(B1,8,""),9,""),"")+0

If you don't want to have the results to be number-formatted, just drop +0 from the latter formula.

Caveat. It will not work on strings like 45WQ423U.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-04-08 16:30 ]

[Damon Ostrander]

Hi Eddie,

I assume you mean the ASCII character codes 65-90 that encompass all upper case standard alphabet characters. Here is a bit of code that I wrote to clean up control characters (it turned out to be totally unnecessary as there is a CLEAN function already in Excel), but the serendipitous result is that it should work fine for your problem. Just select the range you want "cleaned" and run the CleanSelectedRange macro. This code should be placed in a standard macro module.

Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub

Function CleanString(StrIn As String) As String
' "Cleans" a string by removing embedded characters in the ASCII range from 65-90
'This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
Dim ACh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
ACh = Asc(Mid(StrIn, iCh, 1))
If 64 < ACh and ACh < 91 Then
'remove designated character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh

End Function