search cell
 

On Nov 22, 2:02*pm, "Auric__" wrote:
gary wrote:
On Nov 22, 1:03*pm, "Auric__" wrote:
gary wrote:
On Nov 22, 9:49*am, "Auric__" wrote:
gary wrote:
How can I search a cell to find the first two consecutive spaces?

* x = InStr(ActiveCell.Formula, " *")

If the formula is "a b *c *d e", then x = 4.

Here's my data:

* * * * * * * * * * * * * * *Col C

*1 * HILL * * * * * * * KENNETH * * E * * * ** * *III
*2 * HILL * * * * * * * KENNETH * * E * * * ** * *III
*3 * VAN SMAALEN FAMILY TRUST
*4 * VAN SMAALEN FAMILY TRUST
*5 * EXECUTIVE ENT
*6 * PASQUALETTO * * * *RICHARD * * LEE
*7 * JUICE MAN
*8 * JUICE MAN
*9 * HAMER * * * * * * *JAMES * * * R
10 * HAMER * * * * * * *JAMES * * * R

What syntax would your formula have to identify cells C1, C2, C6, C9
and C10 as having two consective spaces?

In the line I posted, if x is greater than 0 (zero), there is a run of
*at least* 2 consecutive spaces:
* x = InStr(ActiveCell.Formula, " *")
* If x Then MsgBox "There are two spaces in cell " & ActiveCell.Address

(Don Guillett's code does essentially the same thing, once you get past
Google's mangling of his line feeds.)

What *exactly* are you trying to accomplish? Be specific.

I want to know if the cell contains two consecutive spaces.
For example:

HILL * * * * * * * KENNETH * * E * * * * * *III * * * *does
VAN SMAALEN FAMILY TRUST * * * * * * * * *does not

(What I've been posting is VBA code.)

Do you need a function that returns something? If so:
* Function has2spaces(what) As Boolean
* * has2spaces = CBool(InStr(what, " *"))
* End Function

You can use this in a cell like so:
* *C * * * * * * * * * * * * * * * * * * * *D
1 *HILL * * * * *KENNETH *E * * * * * *III *=IF(has2Spaces(C1),"Yes","No")
2 *VAN SMAALEN FAMILY TRUST * * * * * * * * =IF(has2Spaces(C2),"Yes","No")

Alternately, you could use the spreadsheet function SEARCH:
* =IF(SEARCH(" *",C1)0,"Yes","No")

--
Just what we need:
extremely cheap cars that crash and burn without cause or warning.- Hide quoted text -

- Show quoted text -


I successfully used: =Search(" ",c1,1)

where " " is the two consecutive spaces, 'c1' is the cell is search
and '1' is the starting position.