|
COUNTIF "string" in cell
Hi All.......
How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
One way:
=COUNTIF(A:A,"*JOHN*") In article , CLR wrote: Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
something like
mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Cool, thanks JE..........just tweaked it a bit for my application...
Sub FindName() Range("B1").Formula = "=COUNTIF(n:n,""*john*"")" End Sub Many thanks.......... Vaya con Dios, Chuck, CABGx3 "JE McGimpsey" wrote: One way: =COUNTIF(A:A,"*JOHN*") In article , CLR wrote: Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Thanks Don..........works good but seems to be "case sensitive".......can
anything be done to change that? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: something like mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Or if you no longer need the formula
'Range("B1").Formula = "=COUNTIF(n:n,""*john*"")" Range("b1").Value = Application.CountIf(Columns("n"), "*john*") -- Don Guillett SalesAid Software "CLR" wrote in message ... Cool, thanks JE..........just tweaked it a bit for my application... Sub FindName() Range("B1").Formula = "=COUNTIF(n:n,""*john*"")" End Sub Many thanks.......... Vaya con Dios, Chuck, CABGx3 "JE McGimpsey" wrote: One way: =COUNTIF(A:A,"*JOHN*") In article , CLR wrote: Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Thanks Don, this new one requires the entire string to be UpperCase. Can it
be modified to be not case sensitive to any of the characters in the string? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: Sub countjohn() For Each c In Selection If InStr(UCase(c), "JOHN") 0 Then mc = mc + 1 Next c MsgBox mc End Sub -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don..........works good but seems to be "case sensitive".......can anything be done to change that? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: something like mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
How slick it is...........thanks Don!
Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: Or if you no longer need the formula 'Range("B1").Formula = "=COUNTIF(n:n,""*john*"")" Range("b1").Value = Application.CountIf(Columns("n"), "*john*") -- Don Guillett SalesAid Software "CLR" wrote in message ... Cool, thanks JE..........just tweaked it a bit for my application... Sub FindName() Range("B1").Formula = "=COUNTIF(n:n,""*john*"")" End Sub Many thanks.......... Vaya con Dios, Chuck, CABGx3 "JE McGimpsey" wrote: One way: =COUNTIF(A:A,"*JOHN*") In article , CLR wrote: Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
???
-- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don, this new one requires the entire string to be UpperCase. Can it be modified to be not case sensitive to any of the characters in the string? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: Sub countjohn() For Each c In Selection If InStr(UCase(c), "JOHN") 0 Then mc = mc + 1 Next c MsgBox mc End Sub -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don..........works good but seems to be "case sensitive".......can anything be done to change that? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: something like mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Sorry, didn't see your other suggestion when I did this post..........your
other one will do me just fine......... Many thanks again, Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: ??? -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don, this new one requires the entire string to be UpperCase. Can it be modified to be not case sensitive to any of the characters in the string? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: Sub countjohn() For Each c In Selection If InStr(UCase(c), "JOHN") 0 Then mc = mc + 1 Next c MsgBox mc End Sub -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don..........works good but seems to be "case sensitive".......can anything be done to change that? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: something like mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
COUNTIF "string" in cell
Glad to help
-- Don Guillett SalesAid Software "CLR" wrote in message ... Sorry, didn't see your other suggestion when I did this post..........your other one will do me just fine......... Many thanks again, Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: ??? -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don, this new one requires the entire string to be UpperCase. Can it be modified to be not case sensitive to any of the characters in the string? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: Sub countjohn() For Each c In Selection If InStr(UCase(c), "JOHN") 0 Then mc = mc + 1 Next c MsgBox mc End Sub -- Don Guillett SalesAid Software "CLR" wrote in message ... Thanks Don..........works good but seems to be "case sensitive".......can anything be done to change that? Vaya con Dios, Chuck, CABGx3 "Don Guillett" wrote: something like mc=0 for each c in selection if instr(c,"John")0 then mc=mc+1 next c msgbox mc -- Don Guillett SalesAid Software "CLR" wrote in message ... Hi All....... How might the code read to perform a check of all cells in the selected column and count all the cells which contain a certain string at least once, and place the total in cell B1. That is, if the string exists by itself, or within a string of other characters in the same cell.....and if multiple times in one cell, only count once. If looking for JOHN, Cell with JOHN ...would count 1 Cell with JOHN/BILL /TOM & OTHERS... would count 1 Cell with JOHN / BILL,JOHN ...would count 1 Cell with BILL, TOM ...would not count (note the inconsistant punctuation) TIA for any help Vaya con Dios, Chuck, CABGx3 |
| All times are GMT +1. The time now is 12:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com