Count of Characters in a Cell

[Michelle]

What is the formula to sort by number of characters in a cell.
I'm trying to conditional format and sort rows with 4 or more characters.

I have two columns which will have airport codes. I'm looking to to find
international airport codes (with 4 characters or more).

Departure Arrival
1 PNS RSW
2 CYYJ OAK
3 ASE CYEG


I would need it to sort and rows of data containing an international airport
codes.
The results being row 2 and 3.

Also, can I highlight certain international codes a certain color?
thanks
Michelle

 

[Klatuu]

Is this an Access question or an Excel question?
There are no cells in an Access database. If this is an Excel question, you
should ask the question in an Excel newsgroup.
If it is an Access question, please provide more detail.

 

[fredg]

What is the formula to sort by number of characters in a cell.
I'm trying to conditional format and sort rows with 4 or more characters.

I have two columns which will have airport codes. I'm looking to to find
international airport codes (with 4 characters or more).

Departure Arrival
1 PNS RSW
2 CYYJ OAK
3 ASE CYEG

I would need it to sort and rows of data containing an international airport
codes.
The results being row 2 and 3.

Also, can I highlight certain international codes a certain color?
thanks
Michelle

I would suggest you quickly learn to use the correct terminology for
Access.
Access does not have cells, it has fields. And by rows, I believe you
mean records. It makes deciphering your message just a bit easier.

To determine the size of an Access text control, in a form or report,
you can use:
Len([Departure])
and
Len([Arrival])

Look up the Len function in VBA help.

If you wish to Conditional Format a control, open the Form (or Report)
in Design View. Select that control.
Click on Format + Conditional Formatting
Set Condition1 to
Expression Is
Set the expression to:
Len([Arrival])=4
Select the color.

Do similar for the [Departure] control.

 

[John Spencer]

If you mean you want to return records where the field has at least 4
characters you can use one of these two methods

FIELD: Departure
Criteria (1): LIKE "????*"
Criteria (2): <<Leave Blank>>

FIELD: Arrival
Criteria (1): <<Leave Blank>>
Criteria (2): LIKE "????*"

OR you can use

Field: DepartureLength: Len(Departure)
Criteria (1): >= 4
Criteria (2): <<Leave Blank>>

Field: ArrivalLength: Len(Arrival)
Criteria (1): <<Leave Blank>>
Criteria (2): >= 4

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County