Text String count within a Cell

Solved/Closed
GRA - Feb 17, 2009 at 08:01 AM
 Mostafa - Oct 14, 2014 at 08:02 AM
Hello,
I have number of records (see below) and want to know if I can use a function to count the number of records that contain a specific text string within each cell entry?.

e.g. records could be

FND Short Job Outcome.5000000\.00
FND Sustained Outcome.5000000\.00
.5000000\.00
.5000000\.00


I need a function to locate the text string "Outcome" and return a value of (in this case) = 2 records from the 4 entries.

Is this possible please?

Thanks,
GRA

4 replies

aquarelle
Posts
7118
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
July 29, 2022
491
Feb 17, 2009 at 03:26 PM
Hello,
I supposed you had put your data in column A and Row 1 to 4 :
                    A
1   FND Short Job Outcome.5000000\.00
2   FND Sustained Outcome.5000000\.00
3   .5000000\.00
4   .5000000\.00


The formula is : =COUNTIF(A1:A4;"*Outcome*")

Best regards
9
Hi,

Can you please let me know if there is a way where a particular text string can be counted within a value of a cell itself.

For example: A cell contains AAAABBBCCCAAA and I need to calculate occurrences of alphabet 'A' in this particular cell.
0
This should do the trick:

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

To find alphabet "B" or any other character, replace the "A" with the appropriate letter, or reference it to another cell where you can store the letter you are looking for, e.g.

=LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))

where B1 now contains "A", or "C", etc.

Hope this helps.
WRL
0
Thank you very much! No VBA code for this brilliant time-saver solution. Well done! Thanks again.
0
great job, thanks
0
Super clever. Thanks, worked like a charm.
2
Oh my god. You are genius, dude.
0
Very clever! Thank you so much:)
0
oh my god thanks very much
0
Wow good solution.
I need to grab a string from a cell that is repeated 2 or 3 times and print it in next cell.

Ex: Cell A1 contains below text..
"Device Requirement Number: ABC-XYZ-036, ABC-MNQ-037"

I want output in A2 as follows:
ABC-XYZ-036, ABC-MNQ-037

ABC is common string which we can use it for search and pick. I used below formula but returns only one ABC**** string, but I want both ( may be sometimes I get ABC repeated 3 times in that column)
=mid(A1,find("ABC",A1),11)

Thank you
Kiran
0