| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search cell
How can I search a cell to find the first two consecutive spaces?
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to search a text in a cell and copy the row to another set of cell | Excel Programming | |||
| how do i make a cell a search cell for a spreadsheet | Excel Worksheet Functions | |||
| FIND / SEARCH text compare cell to string in 3rd cell | Excel Discussion (Misc queries) | |||
| format cell to search text in another cell | Excel Discussion (Misc queries) | |||
| Search Cell Address and Cell Names in VBA Editor | Excel Programming | |||
Hybrid Mode
