Removing non-alpha numeric characters

[PGipe]

I have a large list of part numbers containing different
types of non-alpha numeric characters (/'s, -'s, :'s,
etc). Is there any way to remove these and come up with
just a "normalized" part number containing letters and
numbers only ? I have seen some utilities for sale that
will do this, but thought there might be a way to do it
directly in Excel.

 

[Frank Kabel]

Hi
if you only have a small set of characters within your part number try
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),":","")

 

[Paul B]

PGipe, can you just use replace, Ctrl+h, for this? find ' and leave the
replace with blank

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Trevor Shuttleworth]

You'll need an extra column and input the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"/",""),"-",""),":","")

This assumes your data starts in cell A2. Just drag down or autofill.
You'll need to add the "etc" yourself.

Regards

Trevor

 

[PGipe]

Frank,

Sounds like a possibility. I am trying to avoid a huge
substitue formula, but may need one. The problem is the
list never really contains the same characters to look
for. Every time I get a new list to work with you see
something different (or so it seems). I may tinker around
with Paul B's idea of using the replace function also.

Thanks to both of you for suggestions.

PGipe

 

[Ron Rosenfeld]

I have a large list of part numbers containing different
types of non-alpha numeric characters (/'s, -'s, :'s,
etc). Is there any way to remove these and come up with
just a "normalized" part number containing letters and
numbers only ? I have seen some utilities for sale that
will do this, but thought there might be a way to do it
directly in Excel.

One method is with a User Defined Function (UDF):

============================
Option Explicit
Function Alpha(rg As Range) As String
Dim i As Integer

If rg.Count <> 1 Then
MsgBox ("Only a single cell allowed as argument")
Exit Function
End If

For i = 1 To Len(rg.Text)
Select Case Asc(Mid(rg.Text, i, 1))
Case 48 To 57, 65 To 90, 97 To 122
Alpha = Alpha & Mid(rg.Text, i, 1)
End Select
Next i
End Function
===========================

will strip out everything except 0-9,A-Z,a-z

To enter this UDF, <alt><F11> opens the Visual Basic Editor.
Ensure that your current workbook project is highlighted in the Project
Explorer, then Insert/Module and paste the above code into the window that
opens.

To use this function, enter =Alpha(A1) in a cell.
--ron

 

[Dave Peterson]

If the suggestions don't help...

If your part numbers are like the part numbers we use, then it's a pain.

Our normalized part numbers are 12 characters.
First two are right justified.
next 6 are always numeric (right justified, w/leading 0's)
last 4 are alpha-numeric (right justified).

But lots of people write:

d-1234-a or d-001234-a (or whatever the human eye can determine!)
and it needs to be translated into:
" d001234 a" (w/o the quotes)

If your part numbers are as messed up as the ones I get stuck with, you may want
to post your rules and all the variations.

 

[PGipe]

Ron,

Just tried the approach described below on a new list.
Works Great !! Thanks for the help.

PGipe

 

[Ron Rosenfeld]

Ron,

Just tried the approach described below on a new list.
Works Great !! Thanks for the help.

Thank you for the feedback. Glad that it works for you.


--ron

 

[Dana DeLouis]

Don't know if you would be interested in this. If you can set a vba library
reference to "Microsoft VBScript Regular Expressions 5.5", then maybe you
could use this code:

Function AlphaNumericOnly(s As String) As String
Static Re As RegExp
If Re Is Nothing Then Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
Re.Pattern = "[^A-Za-z0-9]" 'vs. \W due to underscore _ .
AlphaNumericOnly = Re.Replace(s, vbNullString)
End Function

A quick test program...

Sub TestIt()
Dim s As String
s =
"!#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`\abcdefghijk
lmnopqrstuvwxyz{|}~"

Debug.Print s
Debug.Print AlphaNumericOnly(s)
Debug.Print AlphaNumericOnly(s)
End Sub