Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula won't work unless column of data is a hard number
In col A I have 17 character code and in Col B I do a replace 11 characters
so just the first 6 characters are left and in Col B Col A Col B abcdefXXXXXXXXXXX abcdef In col C I want to countif(b1:b100,"abcdef") I will get 0 unless I go back and type in every cell in col B and then the formula counts the correct number of matches. Any help as to how I can correct this without retyping each cell in Col B? I have anywhere from 1000 - 9000 cells to match and count. Thanks in advance for any help. -- Ron |
#2
|
|||
|
|||
Seems like when you are using the replace function in column B your new text
you are replacing is " " (a space) so what you are getting is "abcdef " (note the space after f) instead of "abcdef" Checkl the lenght of the string in cell B1 using LEN(B1) whether its 6 or 7 characters If 7 then either modify your replace command or use the following countif =COUNTIF(B1:B100,"abcdef ") Or instead of using column B to assist in the count use this on column A directly =COUNTIF(A1:A100,"*abcdef*") "Ron" wrote in message ... In col A I have 17 character code and in Col B I do a replace 11 characters so just the first 6 characters are left and in Col B Col A Col B abcdefXXXXXXXXXXX abcdef In col C I want to countif(b1:b100,"abcdef") I will get 0 unless I go back and type in every cell in col B and then the formula counts the correct number of matches. Any help as to how I can correct this without retyping each cell in Col B? I have anywhere from 1000 - 9000 cells to match and count. Thanks in advance for any help. -- Ron |
#3
|
|||
|
|||
Thanks N Harkawat,
Your formula works. I will check the spacing...thank you very much! -- Ron "N Harkawat" wrote: Seems like when you are using the replace function in column B your new text you are replacing is " " (a space) so what you are getting is "abcdef " (note the space after f) instead of "abcdef" Checkl the lenght of the string in cell B1 using LEN(B1) whether its 6 or 7 characters If 7 then either modify your replace command or use the following countif =COUNTIF(B1:B100,"abcdef ") Or instead of using column B to assist in the count use this on column A directly =COUNTIF(A1:A100,"*abcdef*") "Ron" wrote in message ... In col A I have 17 character code and in Col B I do a replace 11 characters so just the first 6 characters are left and in Col B Col A Col B abcdefXXXXXXXXXXX abcdef In col C I want to countif(b1:b100,"abcdef") I will get 0 unless I go back and type in every cell in col B and then the formula counts the correct number of matches. Any help as to how I can correct this without retyping each cell in Col B? I have anywhere from 1000 - 9000 cells to match and count. Thanks in advance for any help. -- Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) | |||
Formula to reference column heading | Excel Worksheet Functions | |||
Formula to Extract Data from a Table | Excel Worksheet Functions |