Search a Column by text length

[kb_63]

I have a large worksheet, which the data was imported. In column C of the
worksheet the text ranges from 8 characters to 11 characters in length. I
need to have all the text in column C to be 10 characters long. Is there a
way to search column C for all cells that don't equal 10 characters long? I
would also like it to stop at each cell that doesn't equal 10 characters so I
can correct the cell. Somewhat like a find and replace. Thank You for your
time.

[Bob Tarburton]

Hi kb

=LEN(C1)
will return the number of characters in C1.
If you copy that down in Column D you can serch that.
OR
=if(LEN(C1)=11,LEFT(C1,10),C1&if(LEN(C1)<9," ","")&if(LEN(C1)<10,"
",""))
will remove the last character if the text is 11 characters or add
spaces up to 10 characters if less than 10 characters (but only within
your 8 to 11 character parameters)

Bob

On Fri, 6 May 2005 12:32:01 -0700, "kb_63"
<kb63@discussions.microsoft.com> wrote:

>I have a large worksheet, which the data was imported. In column C of the
>worksheet the text ranges from 8 characters to 11 characters in length. I
>need to have all the text in column C to be 10 characters long. Is there a
>way to search column C for all cells that don't equal 10 characters long? I
>would also like it to stop at each cell that doesn't equal 10 characters so I
>can correct the cell. Somewhat like a find and replace. Thank You for your
>time.

[Rob]

You need to convert the entire column to the desired length all at once, if
possible, I am assuming.

If you're just going to truncate the text, here's how:


Insert a helper column to the right of the text column.
put this formula in all the cells in the helper column: =MID(A1,1,8)

What this does is reurn the charracters in the text starting a 1, and
returning the next 8 characters

If you need to 'fix' the text in some other way, please specify, perhaps one
of us can help.

Rob


"kb_63" wrote:

> I have a large worksheet, which the data was imported. In column C of the
> worksheet the text ranges from 8 characters to 11 characters in length. I
> need to have all the text in column C to be 10 characters long. Is there a
> way to search column C for all cells that don't equal 10 characters long? I
> would also like it to stop at each cell that doesn't equal 10 characters so I
> can correct the cell. Somewhat like a find and replace. Thank You for your
> time.