Finding Problems in Comma Separated List
 

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.

Finding Problems in Comma Separated List
 

This assumes that valid characters are upper and lower case letters, digits,
spaces, and commas:

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=validd(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200825


"Daren" wrote:

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.

Finding Problems in Comma Separated List
 

Thank you. I copied your code into a new workbook with the data that
actually in column Q. I copied/pasted from Function validd(r As Range) As
Boolean all the way to End Function. I then saved the workbook. However,
when I went to column T and entered =validd(Q2) [column Q is named Product
Category Code] an error message appears that says Ambiguous name detected:
validd. What might I have done wrong and how do I fix it.

Thanks again!

"Gary''s Student" wrote:

This assumes that valid characters are upper and lower case letters, digits,
spaces, and commas:

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=validd(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200825


"Daren" wrote:

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.

Finding Problems in Comma Separated List
 

That message usually means that the function has been entered more than once
in VBA. Make sure that you have pasted one and only copy of the routine.
--
Gary''s Student - gsnu200825


"Daren" wrote:

Thank you. I copied your code into a new workbook with the data that
actually in column Q. I copied/pasted from Function validd(r As Range) As
Boolean all the way to End Function. I then saved the workbook. However,
when I went to column T and entered =validd(Q2) [column Q is named Product
Category Code] an error message appears that says Ambiguous name detected:
validd. What might I have done wrong and how do I fix it.

Thanks again!

"Gary''s Student" wrote:

This assumes that valid characters are upper and lower case letters, digits,
spaces, and commas:

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=validd(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200825


"Daren" wrote:

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.

Finding Problems in Comma Separated List
 

Ok, I cleared it out and left just one UDF in. It worked! Will this UDF
also work for codes that have four characters, such as ZZ01? Thanks!

"Gary''s Student" wrote:

That message usually means that the function has been entered more than once
in VBA. Make sure that you have pasted one and only copy of the routine.
--
Gary''s Student - gsnu200825


"Daren" wrote:

Thank you. I copied your code into a new workbook with the data that
actually in column Q. I copied/pasted from Function validd(r As Range) As
Boolean all the way to End Function. I then saved the workbook. However,
when I went to column T and entered =validd(Q2) [column Q is named Product
Category Code] an error message appears that says Ambiguous name detected:
validd. What might I have done wrong and how do I fix it.

Thanks again!

"Gary''s Student" wrote:

This assumes that valid characters are upper and lower case letters, digits,
spaces, and commas:

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=validd(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200825


"Daren" wrote:

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.

Finding Problems in Comma Separated List
 

Also, I want to be able to run this as a macro in column S to look at column
Q where the product category codes are located. I wrote the following in a
new module in Personal.xls:

Sub CheckProductCategoryCodesForErrantCharacters()

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function
End Sub

What do I need to add to it and correct for it to run properly as a macro?

Thanks!

"Daren" wrote:

Ok, I cleared it out and left just one UDF in. It worked! Will this UDF
also work for codes that have four characters, such as ZZ01? Thanks!

"Gary''s Student" wrote:

That message usually means that the function has been entered more than once
in VBA. Make sure that you have pasted one and only copy of the routine.
--
Gary''s Student - gsnu200825


"Daren" wrote:

Thank you. I copied your code into a new workbook with the data that
actually in column Q. I copied/pasted from Function validd(r As Range) As
Boolean all the way to End Function. I then saved the workbook. However,
when I went to column T and entered =validd(Q2) [column Q is named Product
Category Code] an error message appears that says Ambiguous name detected:
validd. What might I have done wrong and how do I fix it.

Thanks again!

"Gary''s Student" wrote:

This assumes that valid characters are upper and lower case letters, digits,
spaces, and commas:

Function validd(r As Range) As Boolean
v = r.Value
validd = True
For i = 1 To Len(v)
ch = Mid(v, i, 1)
If ch Like "[0-9a-zA-Z]" Or ch = " " Or ch = "," Then
Else
validd = False
Exit Function
End If
Next
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=validd(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200825


"Daren" wrote:

Hello,

I have a comma separated list with codes that is set up as G01, G02, G03,
H01, Y02, B03, A06. There can be many many codes on each row, but they
should only be separated by commas and spaces but no other characters. Is
there a function I can use to show any unnecessary spaces and characters?

Thanks.