Search list for non-alpha characters
 

I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any character
in the name that falls outside that code range I would get a true, otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

This UDF wil do it.
Need help with VBA? See David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes


Function mytest(mystring)
mytest = False
For j = 1 To Len(mystring)
n = Asc(Mid(mystring, j, 1))
Select Case n
Case 65 To 90
temp = False
Case 97 To 122
temp = False
Case Else
temp = True
End Select
If temp Then
mytest = True
exit for
end if
Next j
End Function

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Hello,

I wrote a small routine to do something similar to what you want. You
can adapt this code to suit your needs. It is based on J-Walk's ISLIKE
code.

Sub ISLIKE()
Dim ISLIKE As Boolean
Dim arg() As Variant
Dim pattern As String
Dim iMatch As String

arg = Array("Amazon", "Best Buy", "Target1")

pattern = "*[1234567890]*"
ISLIKE = False
For i = 0 To UBound(arg())
If arg(i) Like pattern Then
ISLIKE = True
iMatch = arg(i)
Exit For
End If
Next i

If ISLIKE Then MsgBox "The cell """ & iMatch & """" & " contains a
number."

End Sub



HTH,
JP


On Oct 19, 12:28 pm, Ted Metro
wrote:
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any character
in the name that falls outside that code range I would get a true, otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

You can do this with the following *array* formula:

=PRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),
1))=97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1))<=122)+
(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),
1))=65)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1))<=90))<0

(as it is an array formula commit with Shift+Ctrl+enter). You can then
copy down.

HTH
Kostis Vezerides

On Oct 19, 7:28 pm, Ted Metro
wrote:
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any character
in the name that falls outside that code range I would get a true, otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Try something like this:
A1: (a company name)

This formula returns TRUE if that name contains any Non-A_to_Z characters:
B1: =SUMPRODUCT(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1 ),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 })*{1;0;1})0

Note: The forumula replaces spaces with the letter "A".

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

How about identifying those entries that *do* contain just the letters A-Z.
Any spaces are accounted for so:

Amazon.com = FALSE
Best Buy = TRUE
Yahoo! = FALSE
Wal-Mart = FALSE
Target = TRUE
Kohl's = FALSE
SPSS = TRUE

Try this array formula** :

=SUM(LEN(SUBSTITUTE(A1,"
",""))-LEN(SUBSTITUTE(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIR ECT("65:90"))),""),"
","")))=LEN(SUBSTITUTE(A1," ",""))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Taking a bit of inspiration from Biff's comment about just counting A-Z
letters:

Try this regular formula (committed with just the ENTER key):
B1: =INDEX(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 }),2)<LEN(A1)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



"Ron Coderre" wrote in message
...
Try something like this:
A1: (a company name)

This formula returns TRUE if that name contains any Non-A_to_Z characters:
B1: =SUMPRODUCT(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1 ),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 })*{1;0;1})0

Note: The forumula replaces spaces with the letter "A".

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first
to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Well, I should've known better than to post a formula that contains spaces!

Here it is in chunks so that line wrap doesn't remove the needed spaces:

=SUM(LEN(SUBSTITUTE(A1," ",""))-
LEN(SUBSTITUTE(SUBSTITUTE(UPPER(A1),
CHAR(ROW(INDIRECT("65:90"))),"")," ","")))
=LEN(SUBSTITUTE(A1," ",""))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How about identifying those entries that *do* contain just the letters
A-Z. Any spaces are accounted for so:

Amazon.com = FALSE
Best Buy = TRUE
Yahoo! = FALSE
Wal-Mart = FALSE
Target = TRUE
Kohl's = FALSE
SPSS = TRUE

Try this array formula** :

=SUM(LEN(SUBSTITUTE(A1,"
",""))-LEN(SUBSTITUTE(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIR ECT("65:90"))),""),"
","")))=LEN(SUBSTITUTE(A1," ",""))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first
to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Darn....The text wrapped, hiding a space in the formula:

There's a space between the quote marks (at the line break):
=INDEX(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 }),2)<LEN(A1)

Broken up....the formula looks like this:
=INDEX(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1)
," ","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1))
,{64,90}),2)<LEN(A1)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Ron Coderre" wrote in message
...
Taking a bit of inspiration from Biff's comment about just counting A-Z
letters:

Try this regular formula (committed with just the ENTER key):
B1: =INDEX(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 }),2)<LEN(A1)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



"Ron Coderre" wrote in message
...
Try something like this:
A1: (a company name)

This formula returns TRUE if that name contains any Non-A_to_Z
characters:
B1: =SUMPRODUCT(FREQUENCY(CODE(MID(SUBSTITUTE(UPPER(A1 ),"
","A"),ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),{64,90 })*{1;0;1})0

Note: The forumula replaces spaces with the letter "A".

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is
something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first
to
make this work.

Any ideas how to search a string for a range of character codes?

Search list for non-alpha characters
 

Thank you everyone for all of your replies!!

"T. Valko" wrote:

Well, I should've known better than to post a formula that contains spaces!

Here it is in chunks so that line wrap doesn't remove the needed spaces:

=SUM(LEN(SUBSTITUTE(A1," ",""))-
LEN(SUBSTITUTE(SUBSTITUTE(UPPER(A1),
CHAR(ROW(INDIRECT("65:90"))),"")," ","")))
=LEN(SUBSTITUTE(A1," ",""))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How about identifying those entries that *do* contain just the letters
A-Z. Any spaces are accounted for so:

Amazon.com = FALSE
Best Buy = TRUE
Yahoo! = FALSE
Wal-Mart = FALSE
Target = TRUE
Kohl's = FALSE
SPSS = TRUE

Try this array formula** :

=SUM(LEN(SUBSTITUTE(A1,"
",""))-LEN(SUBSTITUTE(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIR ECT("65:90"))),""),"
","")))=LEN(SUBSTITUTE(A1," ",""))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Ted Metro" wrote in message
...
I have a list of companies starting in A1 --

Amazon.com
Best Buy
Yahoo!
Wal-Mart
Target
Kohl's
SPSS

I would like a formula I can copy down that would display a true/false
whether or not there is a character in the company name that is something
other than a letter.

The character code ranges are 65-90 and 97-122, so if there is any
character
in the name that falls outside that code range I would get a true,
otherwise
a false.

In the list above the false values would be SPSS, Best Buy and Target,
although I suppose I'll have to remove the spaces out of the name first
to
make this work.

Any ideas how to search a string for a range of character codes?