How to test for cells that contain a specific character set?

[phaustin]

Hello all!

I am trying to identify a list of last names and first names that contain any characters other than a uppercase letter, space or hyphen.

I've been reading the boards, and I see how to test for the existence of a *specific* character or string, but I want to test for characters that are not letters, spaces, or hyphens.

(As I sidenote, I'd like to also test for any cells that begin or end with a space, but I can probably figure that out.)

Thanks!
Pete

 

[taurean]

Welcome to MrExcel.

How about posting some data and expected results? You can look for Excel Jeanie or HTML Maker.

 

[BenMiller]

Here's a solution:

Create the following UDF (which I found here):

Function BigSubstitute(CellToChange As Range, _
NameNumber As String) As String

  Dim X As Long
  Dim Data() As String
  Data = Split(NameNumber, ",")

  If (UBound(Data) + 1) Mod 2 Then
      BigSubstitute = "#MISMATCH!"
      Exit Function
  Else
      BigSubstitute = CellToChange.Value
      For X = 0 To UBound(Data) Step 2
      BigSubstitute = Replace(BigSubstitute, Data(X), _
      Data(X + 1), , , vbTextCompare)
      Next
   End If
End Function

Then, use the following combination of formulas, as follows.

Excel Workbook
	A	B
1	ABC	TRUE
2	aBC	FALSE
3	ABC/	FALSE
4	ABC DEF-GHI	TRUE
Sheet1

Let me know if this is ok!

 

[BenMiller]

Just a note: The ones returning "FALSE" are the ones you're looking for - those either have a special character (other than a space or hyphen), or a lower-case letter. To make it easier, wrap a NOT around the function, like this:

=NOT(AND(EXACT(UPPER(A1),A1),REPT(1,LEN(bigsubstitute(A1,"A,1,b,1,c,1,d,1,e,1,f,1,g,1,h,1,i,1,j,1,k,1,l,1,m,1,n,1,o,1,p,1,q,1,r,1,s,1,t,1,u,1,v,1,w,1,x,1,y,1,z,1, ,1,-,1")))=bigsubstitute(A1,"A,1,b,1,c,1,d,1,e,1,f,1,g,1,h,1,i,1,j,1,k,1,l,1,m,1,n,1,o,1,p,1,q,1,r,1,s,1,t,1,u,1,v,1,w,1,x,1,y,1,z,1, ,1,-,1")))

 

[phaustin]

First, THANK you for your fast response. I really do appreciate it. I am under some time constraints, but didn't want to whine about it. (I mean really, who likes a whiner?)

When I applied the below, and put an 'a' or an 'A' in cell A1, I am only getting the response of #NAME?

I apologize for asking a stupid question, but what am I doing wrong? When I hit F2 to edit the formula, it *does* highlight cell A1, so it does seem to be a valid formula.....

=AND(EXACT(UPPER(A1),A1),REPT(1,LEN(bigsubstitute(A1,"A,1,b,1,c,1,d,1,e,1,f,1,g,1,h,1,i,1,j,1,k,1,l,1,m,1,n,1,o,1,p,1,q,1,r,1,s,1,t,1,u,1,v,1,w,1,x,1,y,1,z,1, ,1,-,1")))=bigsubstitute(A1,"A,1,b,1,c,1,d,1,e,1,f,1,g,1,h,1,i,1,j,1,k,1,l,1,m,1,n,1,o,1,p,1,q,1,r,1,s,1,t,1,u,1,v,1,w,1,x,1,y,1,z,1, ,1,-,1"))

 

[BenMiller]

You have to first create the UDF (User-Defied Formula). Hit ALT+F11, click insert>module and paste the VBA code I gave you. Go back to your sheet and it should work.

 

[phaustin]

Thanks! This is very very cool. It'd be fun to actually understand it. I cannot imagine how you strung this together in the 5 minutes that it took you to respond.

And it almost works. It is producing a "TRUE" is the number 1. It disregards this in all cases. So the following show as TRUE

1
A1
BOB1
1BOB

Any other number correctly produces a FALSE.

 

[phaustin]

Oh, and I just tried it. I tested it in one cell, then began the process of copying it across all suspect rows. I only pasted it into a few thousand rows and my system is floored.. I should assume that this is a run-overnight event?

 

[BenMiller]

Whoa - you first posted at 11:53; I didn't reply until 12:41. And I didn't write the VBA code, I found it online (I posted a link to it before). I just put together the formulas.

As far as the 1 - Gosh, I feel so stupid. When I was testing this, I used 1 as my default number... basically, you'll have to pick some sort of symbol that you do not expect to find in your data at all. Do you have anything like that? (An asterix, a hyphen, a tilde, and a question mark [*,-,~,?] will not work).

Let me know what I could use instead of 1. (Again, this will only be a discrepancy if this is the only symbol in the cell. I'm sure you've noticed, ABC123 calculated correctly, even though there is a 1 in it.)

As far as crashing your system, yes - this will tax your processor. Maybe have an intern copy a few hundred rows at a time

 

[BenMiller]

Oh, and I just tried it. I tested it in one cell, then began the process of copying it across all suspect rows. I only pasted it into a few thousand rows and my system is floored.. I should assume that this is a run-overnight event?

Any update on this? Is it working for you?