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.

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.

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.

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.

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.

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.