Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string/char CONTAINS function?
i am trying to determine if a cell has a partial value in it, for example
cola colb john smith 1 john brown 2 bob smith 3 carly simon 4 john clark 5 tom smith 6 processing column a, top to bottom,for 'smith' would return 10, or 'john' would return 8. any help would be appreciated. btw, if column a is a currency field can you find the '$'. i tried the search function on that kind of field and it doesnt think it is text so struck out. can you 'convert' a cell to text for comparison purposes without having to go change the true underlying format? thanks. jim microsoft.public.excel.worksheet.functions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string/char CONTAINS function?
=countif(a:a,"*smith*")
or =countif(a:a,"*"&b1&"*") if b1 contained Smith. === If I looked in values (not formulas) under the Options button on the Edit|Find dialog, I could find the $ in cells that were formatted as Currency. === You may be able to adjust your formula to make it use text: For instance: =vlookup(""&a1, sheet2!a:c,3,false) or =vlookup(text(a1,"000.00"),sheet2!a:c,3,false) jim sturtz wrote: i am trying to determine if a cell has a partial value in it, for example cola colb john smith 1 john brown 2 bob smith 3 carly simon 4 john clark 5 tom smith 6 processing column a, top to bottom,for 'smith' would return 10, or 'john' would return 8. any help would be appreciated. btw, if column a is a currency field can you find the '$'. i tried the search function on that kind of field and it doesnt think it is text so struck out. can you 'convert' a cell to text for comparison purposes without having to go change the true underlying format? thanks. jim microsoft.public.excel.worksheet.functions -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string/char CONTAINS function?
Use a combination of autofilter and the SUBTOTAL() function:
1. put a header cell on top of each column 2. click the header cell and pull down: Data Filter Autofilter Custom and select contains smith 3. this should display only the smith rows 4. in an un-used cell enter: =SUBTOTAL(9,B2:B100) This form of SUBTOTAL will add-up the values in a column, but ignore cells filtered out. -- Gary's Student "jim sturtz" wrote: i am trying to determine if a cell has a partial value in it, for example cola colb john smith 1 john brown 2 bob smith 3 carly simon 4 john clark 5 tom smith 6 processing column a, top to bottom,for 'smith' would return 10, or 'john' would return 8. any help would be appreciated. btw, if column a is a currency field can you find the '$'. i tried the search function on that kind of field and it doesnt think it is text so struck out. can you 'convert' a cell to text for comparison purposes without having to go change the true underlying format? thanks. jim microsoft.public.excel.worksheet.functions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string/char CONTAINS function?
thanks for the quick replies.
"Gary''s Student" wrote in message ... Use a combination of autofilter and the SUBTOTAL() function: 1. put a header cell on top of each column 2. click the header cell and pull down: Data Filter Autofilter Custom and select contains smith 3. this should display only the smith rows 4. in an un-used cell enter: =SUBTOTAL(9,B2:B100) This form of SUBTOTAL will add-up the values in a column, but ignore cells filtered out. -- Gary's Student "jim sturtz" wrote: i am trying to determine if a cell has a partial value in it, for example cola colb john smith 1 john brown 2 bob smith 3 carly simon 4 john clark 5 tom smith 6 processing column a, top to bottom,for 'smith' would return 10, or 'john' would return 8. any help would be appreciated. btw, if column a is a currency field can you find the '$'. i tried the search function on that kind of field and it doesnt think it is text so struck out. can you 'convert' a cell to text for comparison purposes without having to go change the true underlying format? thanks. jim microsoft.public.excel.worksheet.functions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |