identify a record that has a blank space in the data in a field.

J

JH

I need to pull records that have a blank space somewhere in the data , for
example "type 1" as opposed to "type1" or "help me" as opposed to "helpme".
Your help is much appreciated.
 
D

dolivastro

Assume each record has data points in columns A through D. Then the
best solution is writing a VBA function that you would place in column
E that returns a "1" or "0" depending on the presence of a space.

If, however, you want to avoid VBA, and just stick to worksheet
functions, then do the following:

1. In column E, insert the function =IF(ISERROR(FIND(" ",
A1)),0,FIND(" ", A1)).
2. Copy this to columns F through H. References will adjust
automatically.
3. In column I, enter the function =SUM(E2:H2)

If column I is >0, at least 1 cell has a blank.

Hope this helps,
Dom
 
R

Ron Coderre

You want to "pull records"?

How did you want to do that?
Using AutoFilter? Advanced Filter?
Did you want to use formulas to flag the items then sort and move?

***********
Regards,
Ron

XL2002, WinXP
 
G

Gary''s Student

The following formula will tell you if a cell has blanks in it:

=IF(SUBSTITUTE(A1," ","")=A1,"no blanks","has blanks")
 
J

JH

Thanks Dom that worked.

[email protected] said:
Assume each record has data points in columns A through D. Then the
best solution is writing a VBA function that you would place in column
E that returns a "1" or "0" depending on the presence of a space.

If, however, you want to avoid VBA, and just stick to worksheet
functions, then do the following:

1. In column E, insert the function =IF(ISERROR(FIND(" ",
A1)),0,FIND(" ", A1)).
2. Copy this to columns F through H. References will adjust
automatically.
3. In column I, enter the function =SUM(E2:H2)

If column I is >0, at least 1 cell has a blank.

Hope this helps,
Dom
Click to expand...
 
J

JH

Thanks Ron, Dom answered my question.

Ron Coderre said:
You want to "pull records"?

How did you want to do that?
Using AutoFilter? Advanced Filter?
Did you want to use formulas to flag the items then sort and move?

***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
Top