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

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.

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

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




JH wrote:
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.

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

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


"JH" wrote:

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.

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

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

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


--
Gary's Student


"JH" wrote:

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.

identify a record that has a blank space in the data in a fiel
 

Thanks Dom that worked.

" wrote:

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




JH wrote:
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.

identify a record that has a blank space in the data in a fiel
 

Thanks Ron, Dom answered my question.

"Ron Coderre" wrote:

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


"JH" wrote:

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.