Finding Problems in Comma Separated List

[Daren]

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.

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx

 

[Daren]

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!

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/WritingFunctionsInVBA.aspx


--
Gary''s Student - gsnu200825

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.

 

[Gary''s Student]

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

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!

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/WritingFunctionsInVBA.aspx


--
Gary''s Student - gsnu200825

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.

 

[Daren]

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!

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

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!

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/WritingFunctionsInVBA.aspx


--
Gary''s Student - gsnu200825


:

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.

 

[Daren]

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!

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!

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

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!

:

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/WritingFunctionsInVBA.aspx


--
Gary''s Student - gsnu200825


:

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.