The length of data in a cell 2

[smithcza]

Hi

How can I determine the length of data (all text) in a cell. I want to be able to do something when the length of text in a cell is more than 20 characaters.

Great many thanks,

SmithcZA

 

[Molby]

Have a look at the LEN formula

 

[smithcza]

Hi

I eventually succeeded.

Created a form with two text boxes, fed the one the cell value and the other the cell value trimmed to 20 characters and compared the two textbox values. Probably not the best way but it worked for me, this way I could also see how much too long it is.

SmithcZA

 

[xlbo]

if LEN(Textbox1.text)>20 then
msgbox "This cell has greater than 20 characters"
else
'whatever goes here
end if

However, you mentioned cells - you may simply use a worksheet formula to test....

=IF(LEN(A1)>20,"A1 has >20 characters","<20 characters")

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]

Or to return a boolean (true/false) you could just use ..

=LEN(A1)>20

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci