Excel : Count digits in a cell 1

[somalley]

Hi,

Probably a simple formula but can't seem to find it.

I am looking to identify any cells in a column of data that has less than or more that 15 characters. The field is formated as text.

Any help would be appreciated.

Thanks
Shane

 

[SkipVought]

Hi,

Check out the Len Function.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

Hi Shane,

For future instances if you'd like to stop this or check it upon user input, use Data Validation.

For a True/False value you could use something like this ..

=LEN(A1)>15

HTH

-----------
Regards,
Zack Barresse

 

[somalley]

Thanks for your reply.

However it has brought up another problem. The Data is got throught an ODBC line from a database i have no control over.

It would appear that either the driver of excel is inserting empty spaces to fill the field. (Field length being 15)
All records i check using =LEN(A1) give a result of 15.

Is there a way to remove the blank spaces or not count them?

Thanks

Shane

 

[xlhelp]

=LEN(TRIM(CLEAN(A1)))

Member AAA - Abolish Abused Abbreviations

 

[xlbo]

It is the database that 'inserts' the spaces. If the field is a 15 character fixed length field, that is normal behaviour

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Why the Clean as well as the Trim? Clean is only for non-printable characters.

-----------
Regards,
Zack Barresse

 

[xlbo]

LOL - just in case the text originally came from the web or was transferred at any time over the web - got a nasty habit of picking up unprintables when data gets in contact with web apps...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[somalley]

Thats alot everyone.

Worked a treat.

Shane