Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve
 
Posts: n/a
Default Counting specific text in a cell

The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are in the
cell. The above data will vary in different cells, with up to a combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve



  #3   Report Post  
Steve
 
Posts: n/a
Default

I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve




  #4   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

sorry missed the bit about it all being imported into one cell - any chance
you can use data / text to columns to split it up into mulitple cells? if
not, hopefully someone else will have an idea on how to do this.

Cheers
JulieD

"Steve" wrote in message
...
I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are
in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve






  #5   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

actually, i had another think about it and these formulas should work:
=(LEN(D35)-LEN(SUBSTITUTE(D35," FT ","")))/4
=(LEN(D35)-LEN(SUBSTITUTE(D35," PTF ","")))/5

Cheers
JulieD


"JulieD" wrote in message
...
Hi Steve

sorry missed the bit about it all being imported into one cell - any
chance you can use data / text to columns to split it up into mulitple
cells? if not, hopefully someone else will have an idea on how to do
this.

Cheers
JulieD

"Steve" wrote in message
...
I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are
in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve









  #6   Report Post  
Steve
 
Posts: n/a
Default

I wouldn't know where to begin for that one either, and am guessing it would
be much more efficient if I could keep that data in that one cell, especially
when the various cells ( up to hundreds) may have up to 15 incidents of the
FT & PTF in each cell.

Steve

"JulieD" wrote:

Hi Steve

sorry missed the bit about it all being imported into one cell - any chance
you can use data / text to columns to split it up into mulitple cells? if
not, hopefully someone else will have an idea on how to do this.

Cheers
JulieD

"Steve" wrote in message
...
I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are
in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve






  #7   Report Post  
Myrna Larson
 
Posts: n/a
Default

I think JulieD missed the fact that the text is all in one cell. Try it this
way. Let's assume the text you are looking for (FT or PTF) is in C35

=(LEN(D35)-LEN(SUBSTITUTE(D35,C35,"")))/LEN(C35)

BTW, the formula is case-sensitive. You have to search for FT, not ft or fT,
etc.


On Wed, 26 Jan 2005 08:51:12 -0800, "Steve"
wrote:

I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve





  #8   Report Post  
Steve
 
Posts: n/a
Default

Julie & Myrna,

Thank you both. It works great.

Steve

"Myrna Larson" wrote:

I think JulieD missed the fact that the text is all in one cell. Try it this
way. Let's assume the text you are looking for (FT or PTF) is in C35

=(LEN(D35)-LEN(SUBSTITUTE(D35,C35,"")))/LEN(C35)

BTW, the formula is case-sensitive. You have to search for FT, not ft or fT,
etc.


On Wed, 26 Jan 2005 08:51:12 -0800, "Steve"
wrote:

I did try countif, but could not get it to work.
The information is all in one cell, in this case D35.

=COUNTIF(D35,"* FT *")

This formula is resulting in 1, not 5.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

you can use the COUNTIF function
=COUNTIF(A1:A10,"* FT *")
=COUNTIF(A1:A10,"* PTF *")

Cheers
JulieD

"Steve" wrote in message
...
The following info is imported into one cell.

P5-MPC FT 81,
P5-MPC FT 82,
P5-MPC FT 83,
P5-MPC FT 93,
P6-PPDMO PTF 4,
P6-PPDMO PTF 16

I need to count how many FT's are in the cell, and how many PTF's are in
the
cell. The above data will vary in different cells, with up to a
combination
of possibly 15 total FT's & PTF's in the various cells.

Thanks,

Steve





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 08:10 PM
Moving text from one cell to another. estaban botas Excel Worksheet Functions 1 December 15th 04 03:04 PM
how to hyperlink text to a cell steve New Users to Excel 1 December 14th 04 09:21 PM
Formatting a cell as "text" in the number catagory. Ed Excel Worksheet Functions 3 December 7th 04 08:12 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


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

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

About Us

"It's about Microsoft Excel"