Text String count within a Cell [Solved/Closed]

Report
-
 Mostafa -
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

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
486
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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.
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
Thank you very much! No VBA code for this brilliant time-saver solution. Well done! Thanks again.
great job, thanks
Super clever. Thanks, worked like a charm.
Oh my god. You are genius, dude.
Very clever! Thank you so much:)
oh my god thanks very much
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!