What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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

hi
if you want to id your international flights without sorting and using
conditional formating, you could use something like this...
in conditional formating
formual is.......=len(A2)3....pick a color.
this would highlight all 4 digit flights the color you picked.
set one cell then copy the CF cell. highlight your column and pastespecial
formats.

if you want to sort by number of characters, you would have to use a helper
column using a formula lilke the one above

Regards
FSt1
"Michelle" wrote:

What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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

Thank you FSt1.
It kinda works, i'm not sure why it's highlighting some cells with 3 letters
and some not.

for instance it highighted everything but CYUL AND ABC.
It shouldn't have highlighted PBI, NUL TEB AND PIE
It should have selected and highlighted CYUL.

Tail Date Name Departure Arrival
N 3-Jul Elisa PBI MYNN
N 8-Jul Diane CYUL NUL
N 9-Jul Michelle ABC TEB
N 8-May Jodie PIE MYTB



"FSt1" wrote:

hi
if you want to id your international flights without sorting and using
conditional formating, you could use something like this...
in conditional formating
formual is.......=len(A2)3....pick a color.
this would highlight all 4 digit flights the color you picked.
set one cell then copy the CF cell. highlight your column and pastespecial
formats.

if you want to sort by number of characters, you would have to use a helper
column using a formula lilke the one above

Regards
FSt1
"Michelle" wrote:

What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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

hi
i copied your example data and pasted it into my worksheet then applied the
CF i posted to you. everything works ok on this end so i am
confused/supprised that your are having problems.
len counts spaces as characters. are you sure that all cell with 3
characters don't have spaces also.
if unsure try this.
=len(trim(A2))3
as to why it didn't highlighted a 4 character cell is beyond me.
as i said, i tested 3 character and 4 four character before posting.
i have tested your example data with and without the trim functions.
all work here. so i don't know what to say.

can somesone else supply some insight?
Regards
FSt1

"Michelle" wrote:

Thank you FSt1.
It kinda works, i'm not sure why it's highlighting some cells with 3 letters
and some not.

for instance it highighted everything but CYUL AND ABC.
It shouldn't have highlighted PBI, NUL TEB AND PIE
It should have selected and highlighted CYUL.

Tail Date Name Departure Arrival
N 3-Jul Elisa PBI MYNN
N 8-Jul Diane CYUL NUL
N 9-Jul Michelle ABC TEB
N 8-May Jodie PIE MYTB



"FSt1" wrote:

hi
if you want to id your international flights without sorting and using
conditional formating, you could use something like this...
in conditional formating
formual is.......=len(A2)3....pick a color.
this would highlight all 4 digit flights the color you picked.
set one cell then copy the CF cell. highlight your column and pastespecial
formats.

if you want to sort by number of characters, you would have to use a helper
column using a formula lilke the one above

Regards
FSt1
"Michelle" wrote:

What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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 haven't figured out why it's highlighting the cells myself. However the
weird part is if I replace the cell with another 4 letter airport NOT
starting with the letter 'M'. it works. So why is it not recongnizing the
letter 'M'. hmmmm?

"FSt1" wrote:

hi
i copied your example data and pasted it into my worksheet then applied the
CF i posted to you. everything works ok on this end so i am
confused/supprised that your are having problems.
len counts spaces as characters. are you sure that all cell with 3
characters don't have spaces also.
if unsure try this.
=len(trim(A2))3
as to why it didn't highlighted a 4 character cell is beyond me.
as i said, i tested 3 character and 4 four character before posting.
i have tested your example data with and without the trim functions.
all work here. so i don't know what to say.

can somesone else supply some insight?
Regards
FSt1

"Michelle" wrote:

Thank you FSt1.
It kinda works, i'm not sure why it's highlighting some cells with 3 letters
and some not.

for instance it highighted everything but CYUL AND ABC.
It shouldn't have highlighted PBI, NUL TEB AND PIE
It should have selected and highlighted CYUL.

Tail Date Name Departure Arrival
N 3-Jul Elisa PBI MYNN
N 8-Jul Diane CYUL NUL
N 9-Jul Michelle ABC TEB
N 8-May Jodie PIE MYTB



"FSt1" wrote:

hi
if you want to id your international flights without sorting and using
conditional formating, you could use something like this...
in conditional formating
formual is.......=len(A2)3....pick a color.
this would highlight all 4 digit flights the color you picked.
set one cell then copy the CF cell. highlight your column and pastespecial
formats.

if you want to sort by number of characters, you would have to use a helper
column using a formula lilke the one above

Regards
FSt1
"Michelle" wrote:

What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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

hi
now you're gettng wierd. not suppose to be happening. i never thought i
would never ever say this but you seem to have wierd data. you are reporting
things that simply should not be happening.
as stated, everything i posted to you works great......here. i don't know
why it don't work there. FSt1one.yahoo.com. send it to me. i got to see this.

regards
FSt1
note: i do not respond to unsolicited e-mails.
"Michelle" wrote:

I haven't figured out why it's highlighting the cells myself. However the
weird part is if I replace the cell with another 4 letter airport NOT
starting with the letter 'M'. it works. So why is it not recongnizing the
letter 'M'. hmmmm?

"FSt1" wrote:

hi
i copied your example data and pasted it into my worksheet then applied the
CF i posted to you. everything works ok on this end so i am
confused/supprised that your are having problems.
len counts spaces as characters. are you sure that all cell with 3
characters don't have spaces also.
if unsure try this.
=len(trim(A2))3
as to why it didn't highlighted a 4 character cell is beyond me.
as i said, i tested 3 character and 4 four character before posting.
i have tested your example data with and without the trim functions.
all work here. so i don't know what to say.

can somesone else supply some insight?
Regards
FSt1

"Michelle" wrote:

Thank you FSt1.
It kinda works, i'm not sure why it's highlighting some cells with 3 letters
and some not.

for instance it highighted everything but CYUL AND ABC.
It shouldn't have highlighted PBI, NUL TEB AND PIE
It should have selected and highlighted CYUL.

Tail Date Name Departure Arrival
N 3-Jul Elisa PBI MYNN
N 8-Jul Diane CYUL NUL
N 9-Jul Michelle ABC TEB
N 8-May Jodie PIE MYTB



"FSt1" wrote:

hi
if you want to id your international flights without sorting and using
conditional formating, you could use something like this...
in conditional formating
formual is.......=len(A2)3....pick a color.
this would highlight all 4 digit flights the color you picked.
set one cell then copy the CF cell. highlight your column and pastespecial
formats.

if you want to sort by number of characters, you would have to use a helper
column using a formula lilke the one above

Regards
FSt1
"Michelle" wrote:

What is the formula to sort by number of characters in a cell?
I work for an airline and we have domestic and international flights. I'm trying to sort or conditional format rows by identifly international flights.
Domestic flights have 3 letters or number identifiers
International flights have 4 letters or number identifiers

I have two columns which will have airport codes., departure and arrival 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