Search for non alphanumeric characters
 

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).

Search for non alphanumeric characters
 

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
--
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?


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

examples??

--
Don Guillett
SalesAid Software

"jmdaniel" wrote in message
...
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).

Search for non alphanumeric characters
 

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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?


"jmdaniel" wrote:

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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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

--
Regards,
Tom Ogilvy

"jmdaniel" wrote in message
...
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).

Search for non alphanumeric characters
 

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

"jmdaniel" wrote in message
...
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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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

"ben" wrote:

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?


"jmdaniel" wrote:

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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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


"ben" wrote:

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
--
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?


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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)]


"ben" wrote:

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?


"jmdaniel" wrote:

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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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! :)

"Tom Ogilvy" wrote:

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

"jmdaniel" wrote in message
...
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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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!!

"jmdaniel" wrote:

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


"ben" wrote:

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
--
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?


"jmdaniel" wrote:

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).

Search for non alphanumeric characters
 

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


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

Search for non alphanumeric characters
 

I see, I misread the specification.

--
Regards,
Tom Ogilvy

"jmdaniel" wrote in message
...
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! :)

"Tom Ogilvy" wrote:

YY is an alphanumeric character.

--
Regards,
Tom Ogilvy

"jmdaniel" wrote in message
...
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



"Trent Argante" wrote:

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)]


"jmdaniel" wrote:

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).