A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count first and last characters



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 10:14 AM posted to microsoft.public.excel.worksheet.functions
Elton Law[_2_]
external usenet poster
 
Posts: 94
Default Count first and last characters

Dear Expert,

I have got a file which have a system generated field below. (15 rows)
First character must be either "T", "F", "U".
Last character must be either "D", "S", "U".

How to write a function that can calculate how much "T" or "F" for the first
character under the series.
How to write a function that can calculate how much "D" or "S" for the last
character under the series.

TLD
TLD
FLD
TLD
TND
UU
TNS
TND
TNS
TMD
TMD
TLS
TMS
TLD
TMD

  #2  
Old April 23rd, 2010, 10:20 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Count first and last characters

Hi Elton

Try the below

=SUM(COUNTIF(A:A,{"T","F"}&"*"))
=SUM(COUNTIF(A:A,"*" & {"D","S"}))


--
Jacob (MVP - Excel)


"Elton Law" wrote:

Dear Expert,

I have got a file which have a system generated field below. (15 rows)
First character must be either "T", "F", "U".
Last character must be either "D", "S", "U".

How to write a function that can calculate how much "T" or "F" for the first
character under the series.
How to write a function that can calculate how much "D" or "S" for the last
character under the series.

TLD
TLD
FLD
TLD
TND
UU
TNS
TND
TNS
TMD
TMD
TLS
TMS
TLD
TMD

  #3  
Old April 23rd, 2010, 10:39 AM posted to microsoft.public.excel.worksheet.functions
Elton Law[_2_]
external usenet poster
 
Posts: 94
Default Count first and last characters

Thanks .... nothing can challenge you ....

"Jacob Skaria" wrote:

Hi Elton

Try the below

=SUM(COUNTIF(A:A,{"T","F"}&"*"))
=SUM(COUNTIF(A:A,"*" & {"D","S"}))


--
Jacob (MVP - Excel)


"Elton Law" wrote:

Dear Expert,

I have got a file which have a system generated field below. (15 rows)
First character must be either "T", "F", "U".
Last character must be either "D", "S", "U".

How to write a function that can calculate how much "T" or "F" for the first
character under the series.
How to write a function that can calculate how much "D" or "S" for the last
character under the series.

TLD
TLD
FLD
TLD
TND
UU
TNS
TND
TNS
TMD
TMD
TLS
TMS
TLD
TMD

  #4  
Old April 26th, 2010, 03:56 PM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Count first and last characters

Hi,

you may try this

For counting T or F =SUMPRODUCT(1*(LEFT(D317,1)={"T","F"}))
For counting S or D =SUMPRODUCT(1*(RIGHT(D317,1)={"D","S"}))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Elton Law" wrote in message
...
Dear Expert,

I have got a file which have a system generated field below. (15 rows)
First character must be either "T", "F", "U".
Last character must be either "D", "S", "U".

How to write a function that can calculate how much "T" or "F" for the
first
character under the series.
How to write a function that can calculate how much "D" or "S" for the
last
character under the series.

TLD
TLD
FLD
TLD
TND
UU
TNS
TND
TNS
TMD
TMD
TLS
TMS
TLD
TMD

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.