search cell

  • Thread starter Thread starter gary
  • Start date Start date
  x = InStr(ActiveCell.Formula, "  ")

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

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?
 
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?
Click to expand...

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
 
gary said:
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?
Click to expand...

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.
 
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.
Click to expand...

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

HILL KENNETH E III
does
VAN SMAALEN FAMILY TRUST does not
 
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.
Click to expand...

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

HILL KENNETH E III does
VAN SMAALEN FAMILY TRUST does not
 
gary said:
I want to know if the cell contains two consecutive spaces.
For example:

HILL KENNETH E III does
VAN SMAALEN FAMILY TRUST does not
Click to expand...

(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")
 
Back
Top