search cell
 

How can I search a cell to find the first two consecutive spaces?

search cell
 

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.

--
Evil exists; it is intelligence in the service of entropy.

search cell
 

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.

--
Evil exists; it is intelligence in the service of entropy.

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?

search cell
 

On Nov 22, 12:07*pm, 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.

--
Evil exists; it is intelligence in the service of entropy.

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?

Sub iftwospaces()Dim c As Range
dim i as longFor Each c In Range("c2:c66")For i = 2 To Len(c)If Mid(c,
i, 1) = " " And Mid(c, i + 1, 1) = " " ThenMsgBox "two at row " &
c.Row & " at " & Mid(c, i, 1) & iExit ForEnd IfNext iNext cEnd Sub

search cell
 

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.

--
Evil exists; it is intelligence in the service of entropy.

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.

--
What a long, strange trip it's been.

search cell
 

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.

--
Evil exists; it is intelligence in the service of entropy.

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.

--
What a long, strange trip it's been.- Hide quoted text -

- Show quoted text -

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

HILL KENNETH E III
does
VAN SMAALEN FAMILY TRUST does not

search cell
 

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.

--
Evil exists; it is intelligence in the service of entropy.

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.

--
What a long, strange trip it's been.- Hide quoted text -

- Show quoted text -

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

HILL KENNETH E III does
VAN SMAALEN FAMILY TRUST does not

search cell
 

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.