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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>