How to extract the number initials found in one cell?

**buzzbamm101** · 06-28-2013, 09:53 AM

Hello all,

I’m new here and hope that we can help each other.

I need some help!

I have a team of users that notate in a two columns called Notes and Prescription Notes. The rows are patients with demographic information. All users enter their notes in the same cells. In other words, the new notes are added to the exciting notes.

(02/11/2013 3:51PM PER WEBSITE PT ACTIVE TILL 04/04/2013-JJ; 3/14/2013 4:00PM SPOKE TO PATIENT AND CONFIRMED ADDRESS AND PHONE NUMBER….SCHEDULED FOR 3/15/2013 ORDER#1755 -JCG)

The users date, time, and stamp their initials. The initials are always entered at the end of the notation and with a dash.

Examples: -JD, -JCG, -MM, -VM, -JJ, -MG, and -MS.

What I need help in is to be able to extract the number of times a user notates within two cells in one row. I need a formula that will count the number of times the initials (shown above) is typed. The formula should also accommodate for new notations.

Below is an example of how the results should look, refer to the .jpg.

I have also attached a sample of the data.

Any help will be highly appreciated.

Thank you

Jorge

**daddylonglegs** · 06-28-2013, 10:07 AM

Hello Jorge,

Try using this formula in C4 copied across and down

=(LEN($A4&$B4)-LEN(SUBSTITUTE($A4&$B4,"-"&C$2,"")))/(1+LEN(C$2))

custom format as 0;; if you don't want zeroes to display

**martindwilson** · 06-28-2013, 10:26 AM

in c3
=(LEN($A3)-LEN(SUBSTITUTE($A3,"-"&C$2,"")))/(LEN(C$2)+1)+(LEN($B3)-LEN(SUBSTITUTE($B3,"-"&C$2,"")))/(LEN(C$2)+1) mind you this gives a total count for vm of 2 in row 5 not 1 as you show

**buzzbamm101** · 06-28-2013, 03:11 PM

WOW!!!!!!!

Thank you sooooo much!!

I have been breaking my head and trying to find how to do this.

You have helped me and I am very thankful.

Once again thank you!!

Jorge