CODEWITHSUNDEEP
excelvbaformulacounting
Daniel Couch
Daniel Couch

Reputation: 5

Counting multiple specific letters in a single cell

I need to count a series of letters within a cell. Each is separated by a comma. Example:

CM23721 1066 H,H,H,H
CM23722 1022 L,L,L,L,MH,MH,MH,MH,MH,MH,MH,L
CM23723 1094 H

I would really like a formula to count how many times particular phrases (H,L,MH,M) appear in each cell

This is not my data it would seem that a chimp has put it together before I got my hands on it other wise I'd have just counted this data as I went along but hey ho.

Any help would be great. I am not very good with VBA at all but with a good walkthrough I could do it.

Upvotes: 0

Views: 49

Answers (1)

pnuts
pnuts

Reputation: 59485

If your data is in ColumnA starting Row2, please try adding H into B1, L, into C1, MH into D1 and M into E1, then in B2 copied across and down: 

=(LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")))/LEN(B$1)

Note this counts the M in CM372n and counts H whether on its own or as part of MH.

To avoid double counting H, after the formulae have been populated change B2 to: 

=(LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")))/LEN(B$1)-D2

and copy down to suit.

Upvotes: 1

Related Questions