Search for non alphanumeric characters

[jmdaniel]

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[ben]

try this code out, this also bolds out anything that has characters that are
Alphanumeric, that includes dashes, and I didn't know if you wanted to take
them into account.


Sub findnonalpha()
Dim cel As Range
Dim MPS As String
Dim cou As Integer
For Each cel In ActiveSheet.UsedRange
For cou = 1 To Len(cel)
MPS = UCase(Mid(cel, cou, 1))
If MPS < "0" Or MPS > "9" Then
If MPS < "A" Or MPS > "Z" Then
cel.Font.Bold = True
End If
End If
Next
Next
End Sub

 

[Don Guillett]

examples??

 

[Trent Argante]

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub

 

[jmdaniel]

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[ben]

did the macro i submitted do the same thing?
there migh be non visible characters in some codes

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[Tom Ogilvy]

Assume the part numbers are in column A

Sub MarkParts()
Dim rng as Range
columns(1).Interior.ColorIndex = xlNone
On Error Resume Next
set rng = Columns(1).specialcells(xlConstants,xlTextValues)
On Error goto 0
if rng is nothing then
msgbox "All are OK"
else
rng.Interior.ColorIndex = 3
msgbox "Bad Part Numbers are marked in Red"
End if
End sub

 

[Tom Ogilvy]

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[Toppers]

Hi,
Try:


Sub ValidateParts()
Dim rng As Range, cell As Range
Set ws1 = Worksheets("Sheet1") '<=== Change
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row <=== Change
Set rng = .Range("a2:a" & lastrow)
End With

For Each cell In rng
cell = UCase(cell)
For i = 1 To Len(cell)
If Not Mid(cell, i, 1) Like "[A-Z]" And Not Mid(cell, i, 1) Like
"[0-9]" Then
cell.Font.Bold = True
Exit For
End If
Next i
Next cell

End Sub

did the macro i submitted do the same thing?
there migh be non visible characters in some codes

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]


:

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[jmdaniel]

Ben,

Thanks for looking into this for me. This worked perfect, bolding only those
lines that had a non alphanumeric character, or space. Any way to easily
delete the lines that don't get bolded, as they get analyzed? If that is too
much to ask, never mind, I am very grateful for your help as it is.

Jeff

 

[Trent Argante]

Ben & jmdaniel,
I was confused and told it to only bold P/Ns with letters in it.
I ran Ben's, and his worked.
Ben, thanks for the UsedRange method - it's a handy little pup.
--
Trent Argante
[DC.J(n/a)]

did the macro i submitted do the same thing?
there migh be non visible characters in some codes

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]


:

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[jmdaniel]

That is why I would have expected it to NOT be bolded. I was looking for only
lines that contained non alphanumeric characters.

I was able to get Ben's macro to work great, after noticing that some of the
part numbers in the file I was given had one leading blank space. After using
the TRIM function to clean up the list, everything was fine.

Thanks to all for looking into this for me!

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]


:

I have a worksheet that has 40K rows of part numbers. As these part numbers
are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part numbers, and
perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).

 

[jmdaniel]

Never mind. I found a very cool Excel add-in, at:

http://www.rondebruin.nl/easyfilter.htm

that I used to filter only the bold cells. Worked very slick!!

 

[keepITcool]

this s/b just a bit faster

range.font.bold=false

For each Cell in Range
if cell like "*[!A-Za-z0-9]*" then cell.font.bold=true
next



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Trent Argante wrote in

 

[Tom Ogilvy]

I see, I misread the specification.

--
Regards,
Tom Ogilvy

That is why I would have expected it to NOT be bolded. I was looking for only
lines that contained non alphanumeric characters.

I was able to get Ben's macro to work great, after noticing that some of the
part numbers in the file I was given had one leading blank space. After using
the TRIM function to clean up the list, everything was fine.

Thanks to all for looking into this for me!

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

Interesting. The macro ran quick, but bolded lines I would not have expected,
like:

73YY26048
73YY26057

as well as didn't bold lines I would have expected:

#1265
#1273
2/3/5103



:

This assumes column A from cell 1 to the last working cell in the column.
Adjust the code for your range, if different.

Sub CellBold()
' TPA:20060221T1005E:20060221t1018e
Dim rngRange As Range
Dim strCellRange As String
Dim cel As Variant
Dim intCurrentChar As Integer
Dim i As String

strCellRange = "A1:A" & CStr(ActiveCell.SpecialCells(xlLastCell).Row)
Set rngRange = Range(strCellRange)

For Each cel In rngRange.Cells
For intCurrentChar = 1 To Len(cel.Value)
i = Asc(Mid(cel.Value, intCurrentChar, 1))
If (i > 64 And i < 91) Or (i > 96 And i < 123) Then
cel.Font.Bold = True
Exit For
End If
Next intCurrentChar
Next

Set rngRange = Nothing
End Sub
--
Trent Argante
[DC.J(n/a)]


:

I have a worksheet that has 40K rows of part numbers. As these

part
numbers

are from different suppliers, they have different formats. We are sending
this information out in Purchase Orders, via EDI, and we receive Error
messages when a non alphanumeric character is included a part number.

Is there a way I can use a macro to look at all of these part

numbers,
and

perhaps bold the ones that contain one or more non alphanumeric characters? I
have searched high and low, but have not run across a solution to this
(obviously).