# Counting letters in a series

#### ashagape

##### New Member
Hello,

I watched Excel Magic Trick #223 by ExcelisFun on YouTube about counting single characters in a text string. I know I am pretty close to getting the information I need out of a LARGE spreadsheet I have with tons of data in it. Each of my main cells has a series of letters that are codes for other things. I need to extract the codes and sum them up at the bottom of the column. For instance, some of my cells look like this:

PCP, TP, N, NP, PA
N, NP, PA
PCP, S, PH
NP, PH, TP

I need my end result to be this:

PCP = 2
TP = 2
N = 2
PA = 2
etc.

Is there a way to do this? I have 65 columns and 41 rows of information so I would prefer not to have to add any columns. If I need to change my coding to just one character instead of a string (P instead of PCP) I could.

I hope all of this makes since. Thanks for your help.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### schielrn

##### Well-known Member
Welcome to the Board!

Something like this would work to an extent, but will not count if it appears more than twice in a cell and also would count N in something like NP as well as juse N by itself, so this may not meet your complete needs, but will count some:

=countif(A1:BM45,"*PCP*")

or replace PCP with a cell reference like:

=countif(A1:BM45,"*" & A50 & "*")

For just N you would probably need to check for " N,", but then again if N is the first one it would not count it and if it was the last one it would not count it.

Hope that helps to get you started.

#### pgc01

##### MrExcel MVP
Hi

Can a code appear more than once in a cell?

#### barry houdini

##### MrExcel MVP
Assuming no repeat codes in a single cell try

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&F2&","," "&A\$1:D\$10&",")))

Where F2 contains the code and A1:D10 the range.

#### ashagape

##### New Member
Schielrn,

Thank you. I tried your suggestions and they do produce my desired end result; however, to make my data work with the formulas you provided I would have to change my codes to one letter references. Do you know of a way to get the same information without having to change my codes? I can change them if I have to, but I would prefer to leave them as is if possible.

Thanks.

#### ashagape

##### New Member
Barry houdini,

You formula works great!! Thanks!! I have one more question. I put the formula in my first cell and got it set-up to return my data, then I dragged the cell corner down to incorporate all of the other data. To get the same formula to work for the next column's data do I need to change the first formula from A1 to B1, for example, and then drag it down or is there something I can change in the formula to make it work from one column to another automatically?

Thanks.

#### ashagape

##### New Member
Never mind I think I fixed it!! It had to do with the placement of the \$ sign right?

I changed it to =SUMPRODUCT(--ISNUMBER(SEARCH(" "&\$B44&","," "&D5:D38&",")))

Does that look right? It should always use B44 as reference to my code and then I can check that code total column by column.

#### ashagape

##### New Member
Wait I see what you did with the \$ sign in the D\$5:D\$38 spot. It helped always keep it rows 5-38 instead of it descending to D6:D39, D7:D40, etc. So I want to lock the row range but allow it to copy into the next column. How do I get it to change column letters but not row numbers?

#### ashagape

##### New Member
Got it all working great!! Thanks for your help!

Replies
3
Views
198
Replies
4
Views
142
Replies
0
Views
356
Replies
10
Views
386
Replies
1
Views
496

1,191,482
Messages
5,986,843
Members
440,053
Latest member
jhollingworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back